如何在MySQL中从登录/登出日期表获取会员数量时间演变数据

方法一：使用递归CTE（MySQL 8.0及以上版本） #

MySQL 8.0支持递归公共表表达式（CTE），可以轻松生成指定时间段内的所有日期，结合你已掌握的单日期统计逻辑完成批量计算：

SET @first_date = '2004-01-08 00:00:00';
SET @last_date = '2004-01-16 00:00:00';

WITH date_series AS (
    SELECT @first_date AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_series
    WHERE date_val < @last_date
)
SELECT 
    ds.date_val AS 日期,
    COUNT(m.Name) AS 会员数
FROM date_series ds
LEFT JOIN members m 
    ON m.login_date <= ds.date_val 
    AND m.logout_date >= ds.date_val
GROUP BY ds.date_val
ORDER BY ds.date_val;

代码说明 #

• date_series 通过递归生成从@first_date到@last_date的完整日期序列
• 左连接members表，匹配每个日期下处于有效期的会员记录
• 按日期分组统计会员数量，最终按日期排序输出

方法二：兼容MySQL 5.x版本（无递归CTE） #

如果你的MySQL版本低于8.0，可借助数字辅助表生成日期序列：

1. 创建临时数字表（覆盖你需要的最大日期范围）：

CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-- 如需更长范围，可交叉连接生成更多数字
INSERT INTO nums SELECT n1.n + n2.n*10 FROM nums n1 CROSS JOIN nums n2;

2. 生成日期并统计会员数：

SET @first_date = '2004-01-08 00:00:00';
SET @last_date = '2004-01-16 00:00:00';

SELECT 
    DATE_ADD(@first_date, INTERVAL n DAY) AS 日期,
    COUNT(m.Name) AS 会员数
FROM nums
LEFT JOIN members m 
    ON m.login_date <= DATE_ADD(@first_date, INTERVAL n DAY)
    AND m.logout_date >= DATE_ADD(@first_date, INTERVAL n DAY)
WHERE DATE_ADD(@first_date, INTERVAL n DAY) <= @last_date
GROUP BY DATE_ADD(@first_date, INTERVAL n DAY)
ORDER BY DATE_ADD(@first_date, INTERVAL n DAY);

代码说明 #

• 临时表nums提供数字序列，通过DATE_ADD转换为目标日期
• 左连接会员表统计每日有效会员数，过滤出指定时间段内的日期

以上两种方法均可得到你期望的统计结果，优先推荐递归CTE方案，代码更简洁易维护。

内容的提问来源于stack exchange，提问作者OPERACIONES