mysql中group by分组后查询无数据补0;

mysql经常会用到Group By来进行分组查询,但也经常会遇到一个问题,就是当有where条件时,被where条件过滤的数据不显示了。

例如我有一组数据:

我想查询当日领取数量和当日核销数量;

正常的sql查出的话,假如不存在相关记录

SELECT
	cardId ,
	count( *) count
FROM
	userwechatcard
WHERE
	DATE( FROM_UNIXTIME( consumeTime / 1000 ,
	'%Y%m%d' )) = CURDATE()
AND cardState = 6300
GROUP BY
	cardId
结果查不到任何记录;



因此,我们想实现,即使没有数据,也想让count显示出0而不是空的效果;

解决方案:构建一个包含所有cardId的结果集;然后和我们本来的sql进行左外连接,在最外层利用ifnull函数

sql如下:

SELECT DISTINCT uw.cardId uwci, IFNULL( tb.count, 0) usedCount
		FROM userwechatcard uw
		LEFT JOIN (
					SELECT cardId , count( *) count
					FROM userwechatcard
					WHERE DATE( FROM_UNIXTIME( consumeTime / 1000, '%Y%m%d')) = CURDATE() AND cardState = 6300
					GROUP BY cardId ) AS tb 
		ON uw.cardId = tb.cardId



另外一个:



加强版:

我想讲上述两个结果集合并起来,他们的cardId是相同的,上述图片我忘记加别名了;

解决方案:将两个结果集作为查询来源,并分别起别名使用左外联

sql:

select uwc cardId,a.receivedCount,b.usedCount
	from ( 
		SELECT DISTINCT uw.cardId uwc, IFNULL( tb.count ,0 ) receivedCount
		FROM userwechatcard uw
		LEFT JOIN ( 
				 SELECT cardId , count( * ) count
				 FROM userwechatcard
				 WHERE DATE( FROM_UNIXTIME( getTime / 1000 ,'%Y%m%d' )) = CURDATE()
				 GROUP BY cardId ) AS tb 
		ON uw.cardId = tb.cardId ) a
	LEFT JOIN (
		SELECT DISTINCT uw.cardId uwci, IFNULL( tb.count, 0) usedCount
		FROM userwechatcard uw
		LEFT JOIN (
					SELECT cardId , count( *) count
					FROM userwechatcard
					WHERE DATE( FROM_UNIXTIME( consumeTime / 1000, '%Y%m%d')) = CURDATE() AND cardState = 6300
					GROUP BY cardId ) AS tb 
		ON uw.cardId = tb.cardId  ) b 
		ON a.uwc = b.uwci
结果:






©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页