mysql - GROUP BY 不返回每个特定字段的所有行

Question

考虑数据库:

账户表

随着记录:

客户表

与记录:

我想获取所有客户的姓名和 ID，它们在按客户 ID 组织的 Overdraft 中，所以:

SELECT c.Client_id_number , a.Account_number ,  c.firstName_client , c.lastName_Client , a.Account_balance
FROM Clients AS c 
INNER JOIN Accounts AS a
ON a.Client_id_number = c.Client_id_number
WHERE a.Account_balance < 0
GROUP BY c.Client_id_number;

但这又回来了

意思是客户 1234 的帐户 84 和余额 -4000 没有出现...我哪里出错了？

Answer 1

更新:首先，我应该明确以下几点，你不需要使用“分组依据”，因为你不是汇总，所以你的查询应该是:

SELECT c.Client_id_number , a.Account_number ,  c.firstName_client , c.lastName_Client , a.Account_balance
FROM Clients AS c 
INNER JOIN Accounts AS a
ON a.Client_id_number = c.Client_id_number
WHERE a.Account_balance < 0

我注意到您对“分组依据”概念有疑问，因此您知道需要按“SELECT”中的每个字段分组很重要，除了那些您正在汇总的字段(c.Client_id_number，a .Account_number)

所以，即

SELECT c.Client_id_number , a.Account_number,  sum(a.Account_balance) as balance
FROM Clients AS c 
INNER JOIN Accounts AS a
ON a.Client_id_number = c.Client_id_number
WHERE a.Account_balance < 0
GROUP BY c.Client_id_number, a.Account_number;