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 我需要加入这两个表，并从银行表中得到所有的银行名称，尽管支票表没有任何条目。
但目前我的查询是在支票表只有条目时给出的，所以它只返回一个银行结果.请检查一下，让我知道我失踪的地方！！
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提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39958250
复制相关文章
相似问题
腾讯云开发者
