在SQL中每月选择的整洁方式

Question

我正在努力查找每个月的用户数量。这是我从另一个问题中学到的SQL。

创建月数的部分很容易理解，但很长。我想知道是否有更整洁的方法来编写相同的SQL。谢谢

SELECT
meses.MONTH,
COUNT(Users.user_ID) AS num_of_user
FROM
(
SELECT
1 AS MONTH
UNION
SELECT
2 AS MONTH
UNION
SELECT
3 AS MONTH
UNION
SELECT
4 AS MONTH
UNION
SELECT
5 AS MONTH
UNION
SELECT
6 AS MONTH
UNION
SELECT
7 AS MONTH
UNION
SELECT
8 AS MONTH
UNION
SELECT
9 AS MONTH
UNION
SELECT
10 AS MONTH
UNION
SELECT
11 AS MONTH
UNION
SELECT
12 AS MONTH
) AS meses
LEFT JOIN
Users
ON
meses.month = MONTH(Users.joint_date) AND YEAR(Users.joint_date) = '2000'
GROUP BY
meses.MONTN

Answer 1

在MySQL 8.0中，可以使用递归查询生成序列。

我还建议根据文字日期进行筛选，而不是对要筛选的列应用日期函数：这样效率高得多，并且可以利用users(joint_date)上的索引。

with dates as (
select '2020-01-01' dt
union all select dt + interval 1 month from dates where dt + interval 1 month < '2021-01-01'
)
select d.dt, count(u.user_id) as num_of_users
from dates d
left join users u 
on  u.joint_date >= d.dt
and u.joint_date <  d.dt + interval 1 month
group by d.dt

在早期版本中，确实需要使用union枚举日期。然而，我仍然建议使用文字日期技术。这看起来像：

select '2020-01-01' + interval n.n month as dt, count(u.user_id) as num_of_users
from (select 0 n union all select 2 ... union all select 11) n
left join users u 
on  u.joint_date >= '2020-01-01' + interval n.n month
and u.joint_date <  '2020-01-01' + interval (n.n + 1) month
group by n.n