mysql让count为0的记录也显示出来

统计场景：

统计场景：用MySQL在记录表中统计某些指标在某一段时间内（本周、本月、本年）的值

---

查询原表数据

select * from opr_idx

结果为：

+-------------+----------+
| create_time | index_no |
+-------------+----------+
| 2023-06-30 00:00:00 | IDX01 |
| 2023-06-29 00:00:00 | IDX01 |
| 2023-06-05 00:00:00 | IDX02 |
| 2023-06-01 00:00:00 | IDX02 |
| 2023-01-01 00:00:00 | IDX03 |
+-------------+----------+

---

遇到问题

本周、本月、本年统计出来的指标个数不一致。

例子——
统计本周

SELECT
	index_no,count( * ) 
FROM
	opr_idx 
WHERE
	WEEK ( date_format( create_time, '%Y-%m-%d' ) ) = WEEK ( date_format( curdate( ), '%Y-%m-%d' ) ) 
	AND date_format( create_time, '%Y' ) = date_format( curdate( ), '%Y' ) 
GROUP BY
	index_no;

结果

+----------+----------+
| index_no | count(*) |
+----------+----------+
| IDX01 | 2 |
+----------+----------+

---

统计本月

SELECT
	index_no,count( * ) 
FROM
	opr_idx 
WHERE
	date_format( create_time, '%Y-%m' ) = date_format( curdate( ), '%Y%m' ) 
GROUP BY
	index_no;

结果

+----------+----------+
| index_no | count(*) |
+----------+----------+
| IDX01 | 2 |
| IDX02 | 2 |
+----------+----------+

---

统计本年

SELECT
	index_no,count( * ) 
FROM
	opr_idx 
WHERE
	date_format( create_time, '%Y' ) = date_format( curdate( ), '%Y' ) 
GROUP BY
	index_no;

结果

+----------+----------+
| index_no | count(*) |
+----------+----------+
| IDX01 | 2 |
| IDX02 | 2 |
| IDX03 | 1 |
+----------+----------+

---

原因分析：

mysql中count+group by只统计where中的结果，本周中获取不到IDX02、IDX03，本月中获取不到IDX03。

---

解决方案：

方案一：
1、创建一个指标表，将所有指标名称存起来；
2、将两张表进行left join关联。

执行查询指标表MySQL语句

select * from idx_name

结果

+----+----------+
| id | index_no |
+----+----------+
| 1 | IDX01 |
| 2 | IDX02 |
| 3 | IDX03 |
+----+----------+

优化原统计sql

SELECT
	t1.index_no,count( * ) 
FROM
	idx_name t1
LEFT JOIN 
	opr_idx t2 
ON 
	t1.index_no = t2.index_no 
	AND yearweek( date_format( t2.create_time, '%Y-%m-%d' ) ) = date_format( curdate( ), '%Y-%m-%d' ) ) 
GROUP BY
	t1.index_no;

结果

+----------+----------+
| index_no | count(*) |
+----------+----------+
| IDX01 | 2 |
| IDX02 | 0 |
| IDX03 | 0 |
+----------+----------+

方案二：
1、使用union select建一个临时的表，将所有指标名称存起来；
2、将两张表进行left join关联。
优点：不用创建一个张表一直存着指标名，快速查询。
缺点：指标名多的时候，语句不好看。

执行MySQL语句

SELECT
	t1.index_no,count(*) 
FROM
	(select 'IDX01' as index_no union 'IDX02'union 'IDX03') t1
LEFT JOIN 
	opr_idx t2 
ON 
	t1.index_no = t2.index_no 
	AND yearweek( date_format( t2.create_time, '%Y-%m-%d' ) ) = date_format( curdate( ), '%Y-%m-%d' ) ) 
GROUP BY
	t1.index_no;

结果

+----------+----------+
| index_no | count(*) |
+----------+----------+
| IDX01 | 2 |
| IDX02 | 0 |
| IDX03 | 0 |
+----------+----------+

---

完美解决问题~第一次在CSDN码文章，希望对大家有用，欢迎大家批评指正。
我后续将记录日常遇到的bug和有趣的问题，大家动动小手，点点关注。
转发记得标明出处，谢谢。