如何修改SQL查询，使无取消行程的日期也输出0取消量？

原SQL的问题在于：先过滤出已取消的行程记录再按日期分组，因此没有取消行程的日期不会出现在结果集中。要实现无取消日期也显示request_at和0，可以用以下两种方案：

方案一：使用日期维度表左连接（推荐） #

如果需要覆盖所有需要统计的日期（哪怕该日期没有任何行程记录），需要先构建一个包含目标日期范围的维度表，再通过左连接关联行程表统计取消数量。

示例代码（以PostgreSQL为例） #

-- 生成包含所有需统计日期的临时范围表
WITH date_range AS (
    SELECT generate_series(
        (SELECT MIN(request_at) FROM trip),  -- 动态获取行程表最早日期
        (SELECT MAX(request_at) FROM trip),  -- 动态获取行程表最晚日期
        '1 day'::interval
    ) AS request_at
)
SELECT 
    dr.request_at::date,  -- 确保日期格式统一
    COUNT(t.trip_id) AS cancelled  -- 统计匹配的取消行程，无匹配则为0
FROM date_range dr
LEFT JOIN trip t 
    ON dr.request_at::date = t.request_at
    AND t.status IN ('cancelled_by_client', 'cancelled_by_driver')  -- 将取消条件放在JOIN中，避免过滤掉无取消的日期
GROUP BY dr.request_at::date
ORDER BY dr.request_at::date;

其他数据库适配 #

• MySQL 8.0+ 用递归CTE生成日期范围：

WITH RECURSIVE date_range AS (
    SELECT MIN(request_at) AS request_at FROM trip
    UNION ALL
    SELECT request_at + INTERVAL 1 DAY FROM date_range WHERE request_at < (SELECT MAX(request_at) FROM trip)
)
SELECT 
    dr.request_at,
    COUNT(t.trip_id) AS cancelled
FROM date_range dr
LEFT JOIN trip t 
    ON dr.request_at = t.request_at
    AND t.status IN ('cancelled_by_client', 'cancelled_by_driver')
GROUP BY dr.request_at
ORDER BY dr.request_at;

方案二：条件聚合（适用于行程表包含所有需统计日期） #

如果trip表中每个需统计的日期至少有一条行程记录（哪怕是未取消的），可以直接用条件聚合统计：

SELECT 
    request_at,
    SUM(
        CASE 
            WHEN status IN ('cancelled_by_client', 'cancelled_by_driver') THEN 1 
            ELSE 0 
        END
    ) AS cancelled
FROM trip
GROUP BY request_at
ORDER BY request_at;

说明 #

这种方式先按日期分组，再通过CASE WHEN判断每条记录是否为取消状态，最后求和得到取消数量。无取消行程的日期会自然返回0。

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