group by where 查出的数据比预期少？？

最新推荐文章于 2025-02-14 22:47:19 发布

原创最新推荐文章于 2025-02-14 22:47:19 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

8 篇文章

订阅专栏

本文探讨了SQL查询中左连接(left join)与WHERE子句的使用技巧，通过对比不同写法的SQL语句，揭示了如何避免数据丢失，确保查询结果的完整性和准确性。特别关注了在涉及多个表关联查询时，WHERE条件的位置对结果集的影响。

SELECT m.`full_name` AS "商户名称", m.`code` AS "商户编号", s.`full_name` AS "门店名称", s.`code` AS "门店编号", MIN(s.`create_time` ) AS '门店进件日期', IFNULL(SUM(amount), 0) AS "交易总金额", IFNULL(COUNT(*),0) AS "交易总笔数", cm.`name` AS "所属客户经理"

FROM

`store` s LEFT JOIN base_charge bc ON bc.`store_id`=s.`id`

LEFT JOIN `merchant` m ON bc.`merchant_id`=m.`id`

LEFT JOIN `customer_manager` cm ON m.account_manager_id=cm.`id`

WHERE s.`create_time` BETWEEN '2020-01-01 00:00:00' AND '2020-06-30 23:59:59' AND bc.create_time BETWEEN '2020-01-01 00:00:00' AND '2020-06-30 23:59:59'

GROUP BY bc.store_id, s.`full_name`, m.`full_name`, m.`code`, cm.`name` ;

#查出的数据会比预期的要少。

#原因：表先做的关联操作，然后用where里的条件筛选关联后的结果，由于 `store` s LEFT JOIN base_charge 其中字段create_time会有一些为NULL的值，这些代表门店没有交易，但是还是需要，然后经过bc.create_time BETWEEN筛选,会把这部分数据丢掉。

可修改成如下sql：

SELECT m.`full_name` AS "商户名称", m.`code` AS "商户编号", s.`full_name` AS "门店名称", s.`code` AS "门店编号", s.`create_time` AS '门店进件日期', IFNULL(bc.amount, 0) AS "交易总金额", IFNULL(bc.tol,0) AS "交易总笔数", cm.`name` AS "所属客户经理"

FROM

`store` s LEFT JOIN (

SELECT store_id, SUM(amount) AS 'amount', COUNT(*) tol

FROM base_charge

WHERE create_time BETWEEN '2020-01-01 00:00:00' AND '2020-07-19 23:59:59' AND state='00' AND method='xft.trade.pay'

GROUP BY store_id

) bc ON bc.`store_id`=s.`id`

LEFT JOIN `merchant` m ON s.`merchant_id`=m.`id`

LEFT JOIN `customer_manager` cm ON m.account_manager_id=cm.`id`

WHERE s.`create_time` BETWEEN '2020-01-01 00:00:00' AND '2020-06-30 23:59:59'

#由于`store` s位于关联表的最外层，所以关联前后该表的字段都有值，所以：where里s.`create_time` BETWEEN条件，不会造成数据丢失。

注：关于left join的查询，一定要注意where条件作用在关联关联后的结果上，还是作用在关联前的表上。