需求
统计各地市新增用户情况,要求为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