如何用单条SQL查询按日统计用户日程耗时，无记录日期返回0？

当然没问题，核心思路是先构建目标日期范围内的完整日期序列，再将这个序列与你的用户日程表做左连接，最后对耗时进行聚合统计——这样没有日志记录的日期就会自动填充为0了。

下面针对不同数据库给出具体实现，这里以示例中cd_user=123、日期范围为现有日志的最小到最大日期为例：

1. MySQL 8.0+ 版本 #

利用递归CTE生成日期序列：

WITH date_range AS (
    -- 初始化：取该用户最早的日志日期
    SELECT MIN(STR_TO_DATE(dt_log, '%d/%m/%Y')) AS log_date
    FROM your_table
    WHERE cd_user = 123
    UNION ALL
    -- 递归生成后续日期，直到最晚的日志日期
    SELECT DATE_ADD(log_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE log_date < (SELECT MAX(STR_TO_DATE(dt_log, '%d/%m/%Y')) FROM your_table WHERE cd_user = 123)
)
SELECT 
    dr.log_date,
    -- 用COALESCE把NULL（无日志的日期）转为0
    COALESCE(SUM(ts.time_spent), 0) AS total_time_spent
FROM date_range dr
LEFT JOIN your_table ts 
    ON dr.log_date = STR_TO_DATE(ts.dt_log, '%d/%m/%Y')
    AND ts.cd_user = 123
GROUP BY dr.log_date
ORDER BY dr.log_date;

2. PostgreSQL 版本 #

PostgreSQL自带generate_series函数，生成日期序列更简洁：

WITH date_range AS (
    SELECT generate_series(
        (SELECT MIN(to_date(dt_log, 'DD/MM/YYYY')) FROM your_table WHERE cd_user = 123),
        (SELECT MAX(to_date(dt_log, 'DD/MM/YYYY')) FROM your_table WHERE cd_user = 123),
        INTERVAL '1 day'
    )::DATE AS log_date
)
SELECT 
    dr.log_date,
    COALESCE(SUM(ts.time_spent), 0) AS total_time_spent
FROM date_range dr
LEFT JOIN your_table ts 
    ON dr.log_date = to_date(ts.dt_log, 'DD/MM/YYYY')
    AND ts.cd_user = 123
GROUP BY dr.log_date
ORDER BY dr.log_date;

3. SQL Server 版本 #

同样用递归CTE，注意处理递归次数限制：

WITH date_range AS (
    SELECT CAST(MIN(CONVERT(DATE, dt_log, 103)) AS DATE) AS log_date
    FROM your_table
    WHERE cd_user = 123
    UNION ALL
    SELECT DATEADD(DAY, 1, log_date)
    FROM date_range
    WHERE log_date < (SELECT CAST(MAX(CONVERT(DATE, dt_log, 103)) AS DATE) FROM your_table WHERE cd_user = 123)
)
SELECT 
    dr.log_date,
    -- SQL Server用ISNULL替代COALESCE也可以
    ISNULL(SUM(ts.time_spent), 0) AS total_time_spent
FROM date_range dr
LEFT JOIN your_table ts 
    ON dr.log_date = CONVERT(DATE, ts.dt_log, 103)
    AND ts.cd_user = 123
GROUP BY dr.log_date
ORDER BY dr.log_date
OPTION (MAXRECURSION 0); -- 日期范围超过100天时必须加这个参数

扩展：统计所有用户的每日耗时 #

如果需要每个用户的每日统计（无日志的日期也返回0），只需在日期序列基础上交叉连接用户列表即可：

-- 以MySQL为例
WITH date_range AS (
    SELECT MIN(STR_TO_DATE(dt_log, '%d/%m/%Y')) AS log_date
    FROM your_table
    UNION ALL
    SELECT DATE_ADD(log_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE log_date < (SELECT MAX(STR_TO_DATE(dt_log, '%d/%m/%Y')) FROM your_table)
),
user_list AS (
    SELECT DISTINCT cd_user FROM your_table
)
SELECT 
    ul.cd_user,
    dr.log_date,
    COALESCE(SUM(ts.time_spent), 0) AS total_time_spent
FROM user_list ul
CROSS JOIN date_range dr
LEFT JOIN your_table ts 
    ON ul.cd_user = ts.cd_user
    AND dr.log_date = STR_TO_DATE(ts.dt_log, '%d/%m/%Y')
GROUP BY ul.cd_user, dr.log_date
ORDER BY ul.cd_user, dr.log_date;

注意事项 #

• 如果你的dt_log是字符串类型，一定要先转为日期格式（比如示例中的STR_TO_DATE/to_date/CONVERT），否则日期比较会出错。
• 如果需要固定日期范围（比如某一个月），直接替换CTE中MIN/MAX的部分为固定日期即可，比如'2018-01-01'和'2018-01-31'。

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