在Mysql中对任意值进行迭代

Question

基于这个答案，我有一个在Mysql中生成日期列表的查询，例如：

+-----------------------------------------------+
| Month beginning                               |
+-----------------------------------------------+
| 2022-10-01                                    |
| 2022-09-01                                    |
| 2022-08-01                                    |
| 2022-07-01                                    |
| 2022-06-01                                    |
| 2022-05-01                                    |
| 2022-04-01                                    |
| 2022-03-01                                    |
| 2022-02-01                                    |
+-----------------------------------------------+

我还有一个查询，用于计算截至9月底尚未登录的用户数量：

SET @range_end = "2022-10-01"; SELECT (SELECT COUNT(DISTINCT id) FROM users WHERE timestamp < @range_end) - (SELECT COUNT(DISTINCT user_id) FROM logins WHERE timestamp < @range_end) AS "Not logged in";

这只是我试图解决的问题的一个例子——我很感激有更简单的计算方法。

如何组合这些查询，以便在用户计数查询中使用Month Beginning查询生成的日期？以下示例：

+-----------------+---------------+
| Month beginning | Not logged in |
+-----------------+---------------+
| 2022-10-01      |           110 |
| 2022-09-01      |           120 |
| 2022-08-01      |           130 |
| 2022-07-01      |           140 |
| 2022-06-01      |           150 |
| 2022-05-01      |           160 |
| 2022-04-01      |           170 |
| 2022-03-01      |           180 |
| 2022-02-01      |           200 |
+-----------------+---------------+

我尝试过各种联接、联合、分组和子查询，但都无法坚持下去。

或者我试图做的事情是否违反了子查询限制。

月份生成查询为：

select DATE_FORMAT(date_range.timestamp, "%Y-%m-01") AS "Month beginning"
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as timestamp
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) date_range
where date_range.timestamp between '2022-02-28' and NOW() GROUP BY DATE_FORMAT(date_range.timestamp, "%Y-%m-01") ORDER BY timestamp DESC;

Answer 1

使用子查询作为表从中进行选择

SELECT range_end AS "Month beginning", 
(SELECT COUNT(DISTINCT id) FROM users WHERE timestamp < range_end) - 
(SELECT COUNT(DISTINCT user_id) FROM logins WHERE timestamp < range_end)    
AS "Not logged in"
FROM (
select DATE_FORMAT(date_range.timestamp, "%Y-%m-01") AS range_end
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as timestamp
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) date_range
where date_range.timestamp between '2022-02-28' and NOW() GROUP BY DATE_FORMAT(date_range.timestamp, "%Y-%m-01") ORDER BY timestamp DESC
) AS date_range