MYSQL左加入结果未给出
MYSQL左加入结果未给出
提问于 2016-10-10 12:25:22
我有两张桌子
银行表
create table `banks` (
`bank_id` int ,
`bank_name` varchar (150),
`balance` double ,
`b_date` date ,
`delete_state` double
);
insert into `banks` (`bank_id`, `bank_name`, `balance`, `b_date`, `delete_state`) values('1','Emirates NBD','632008','2016-10-10','0');
insert into `banks` (`bank_id`, `bank_name`, `balance`, `b_date`, `delete_state`) values('3','HABIB BANK LIMITED','1134484','2016-10-10','0');支票表
create table `cheque` (
`ch_id` int ,
`bank_id` int ,
`amount` double ,
`status` int,
`delete_state` double
);
insert into `cheque` (`ch_id`, `bank_id`, `amount`, `status`, `delete_state`) values('4','1','15000','2','0');
insert into `cheque` (`ch_id`, `bank_id`, `amount`, `status`, `delete_state`) values('9','1','250000','1','0');我的MYSQL查询是
SELECT bk.*, SUM(amount) AS tot_amount, (bk.balance - SUM(amount)) AS bank_balance FROM banks bk LEFT JOIN cheque ch ON bk.bank_id = ch.bank_id
WHERE ch.status = 1 AND bk.delete_state=0 AND ch.delete_state = 0 我需要加入这两个表，并从银行表中得到所有的银行名称，尽管支票表没有任何条目。
但目前我的查询是在支票表只有条目时给出的，所以它只返回一个银行结果.请检查一下，让我知道我失踪的地方！！
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-10-10 12:38:10
您需要按bank_id进行分组。当您将问题分组时，您将得到组中变量的每个值的结果。
SELECT bk.*, SUM(amount) AS tot_amount, (bk.balance - SUM(amount)) AS bank_balance
FROM banks bk
LEFT JOIN cheque ch ON (bk.bank_id = ch.bank_id AND ch.status = 1 AND ch.delete_state = 0)
WHERE bk.delete_state=0
GROUP BY bk.bank_id;SQL Fiddle
Stack Overflow用户
发布于 2016-10-10 12:40:02
试试这个：
SELECT bk.*,
SUM(CASE WHEN bk.delete_state=0 THEN amount ELSE 0 END) AS tot_amount,
SUM(CASE WHEN bk.delete_state=0 THEN bk.balance ELSE 0 END)
-
SUM(CASE WHEN bk.delete_state=0 THEN amount ELSE 0 END) AS bank_balance
FROM banks bk
LEFT JOIN cheque ch ON bk.bank_id = ch.bank_id AND ch.status = 1 AND ch.delete_state = 0
GROUP BY bk.`bank_id`, bk.`bank_name`几个注意事项：
- 该查询使用一个
GROUP BY子句，因此a为每个bank_id返回一个单独的记录。 - 与
cheque表相关的谓词已从WHERE转移到ON子句。否则，LEFT JOIN就变成了INNER JOIN。 - 该查询使用条件聚合，以便只考虑与拥有
bk.delete_state=0的银行相关的记录。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39958250
复制相关文章
相似问题
腾讯云开发者
