MySQL分组后如何关联匹配其他表的全部数据行？

核心问题 #

你得到3行结果的直接原因是不必要的GROUP BY dogs_owners.account——这个子句会将同一个account下的所有记录聚合为单行，把原本100+条明细结果压缩成3行（对应3个唯一account）。如果要获取所有匹配的目标数据行，必须移除这个GROUP BY。

另外你的SQL还存在两处语法/逻辑错误：

• 第一个JOIN条件错误：dogs_owners.acc_number = dogs_owners.account是同表自关联，毫无意义，应该是dogs表与dogs_owners表的关联字段（比如dogs.acc_number = dogs_owners.account，需根据实际表结构调整）。
• 关联support_info_row时拼写错误：dog_owners.acc_number应为dogs_owners.acc_number。

修正后的SQL语句 #

1. 获取所有符合条件的dogs与dogs_owners明细行

SELECT * 
FROM dogs
JOIN dogs_owners ON dogs.acc_number = dogs_owners.account -- 修正为跨表关联条件，需根据实际表结构调整字段
WHERE dogs.project_id = 5000
AND dogs_owners.type = "cute"

2. 关联support_info_row获取所有匹配account的行

SELECT * 
FROM dogs
JOIN dogs_owners ON dogs.acc_number = dogs_owners.account
JOIN support_info_row ON support_info_row.account = dogs_owners.acc_number -- 修正拼写错误
WHERE dogs.project_id = 5000
AND dogs_owners.type = "cute"

3. 关联support_info并按日期范围筛选

SELECT * 
FROM dogs
JOIN dogs_owners ON dogs.acc_number = dogs_owners.account
JOIN support_info_row ON support_info_row.account = dogs_owners.acc_number
JOIN support_info ON support_info.row_id = support_info_row.id 
WHERE dogs.project_id = 5000
AND dogs_owners.type = "cute"
AND support_info.date BETWEEN '2024-01-01' AND '2024-12-31' -- 替换为实际日期值，注意用单引号包裹

补充说明 #

如果后续需要对数据做聚合统计（比如按account计数、求和），再添加GROUP BY，但此时不能用SELECT *，必须明确指定聚合字段和聚合函数（比如SELECT dogs_owners.account, COUNT(*) FROM ... GROUP BY dogs_owners.account）。

内容的提问来源于stack exchange，提问作者PPP