group by 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条件作用在关联关联后的结果上，还是作用在关联前的表上。