MySQL 统计count为0的情况 LEFT JOIN 关联查询左表数据缺失的问题

需求

统计各地市新增用户情况，要求为0的也展示。

实现方案

构造所有地市为左表，关联用户表统计查询
like this：

SELECT
	t.phone_place,
	count( h.id ) AS count 
FROM
	(
	SELECT
		'安顺' phone_place UNION
	SELECT
		'遵义' phone_place UNION
	SELECT
		'黔东南' phone_place UNION
	SELECT
		'毕节' phone_place UNION
	SELECT
		'六盘水' phone_place UNION
	SELECT
		'铜仁' phone_place UNION
	SELECT
		'贵阳' phone_place UNION
	SELECT
		'黔南' phone_place UNION
	SELECT
		'黔西南' phone_place 
	) t
	LEFT JOIN app_register h ON t.phone_place = h.phone_place 
WHERE
	h.create_time > '2021-11-06 23:23:42' 
	AND h.create_time < '2021-11-10 23:23:42' 
GROUP BY
	t.phone_place 
ORDER BY
	count DESC

结果

安顺市新增用户为0，但是在查询结果中不显示，左表缺失。

原因

在LEFT JOIN后添加的筛选条件为右表app_resgister中的create_time字段，因此左表中统计不到为0的情况，相当于RIGHT JOIN右关联app_register表。

解决

将筛选条件写到LEFT JOIN 之后。
like this：

SELECT
	t.phone_place,
	count( h.id ) AS count 
FROM
	(
	SELECT
		'安顺' phone_place UNION
	SELECT
		'遵义' phone_place UNION
	SELECT
		'黔东南' phone_place UNION
	SELECT
		'毕节' phone_place UNION
	SELECT
		'六盘水' phone_place UNION
	SELECT
		'铜仁' phone_place UNION
	SELECT
		'贵阳' phone_place UNION
	SELECT
		'黔南' phone_place UNION
	SELECT
		'黔西南' phone_place 
	) t
	LEFT JOIN ( 
	SELECT id, phone_place FROM app_register
	 WHERE create_time > '2021-11-06 23:23:42' 
	 AND create_time < '2021-11-10 23:23:42'
		) h ON t.phone_place = h.phone_place 
GROUP BY
	t.phone_place 
ORDER BY
	count DESC

顺利解决

记录一下。over