如何用SQL统计日期区间内每日在途车辆数量？

现有数据表 #

假设你的表名为car_trips，结构及数据如下：

期望输出 #

需要得到指定日期区间内，每天处于出行状态的车辆数量：

实现方案 #

核心思路：先生成目标日期区间的连续日期序列，再关联原表统计每个日期下符合出行状态的车辆数（即车辆的出发日期≤当前日期，且到达日期≥当前日期）。

方案1：适用于支持递归CTE的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）

WITH date_range AS (
    -- 定义起始和结束日期，按需修改
    SELECT STR_TO_DATE('12-03-2022', '%d-%m-%Y') AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < STR_TO_DATE('17-03-2022', '%d-%m-%Y')
)
SELECT
    DATE_FORMAT(dr.date_val, '%d-%m-%Y') AS Date,
    COUNT(c.carid) AS amount
FROM date_range dr
LEFT JOIN car_trips c
    ON dr.date_val BETWEEN STR_TO_DATE(c.`date of departure`, '%d-%m-%Y') 
                       AND STR_TO_DATE(c.`date of arrival`, '%d-%m-%Y')
GROUP BY dr.date_val
ORDER BY dr.date_val;

方案2：适用于不支持递归CTE的数据库（如MySQL 5.x）

通过数字表生成连续日期：

SELECT
    DATE_FORMAT(d.date_val, '%d-%m-%Y') AS Date,
    COUNT(c.carid) AS amount
FROM (
    SELECT 
        STR_TO_DATE('12-03-2022', '%d-%m-%Y') + INTERVAL (a.num + b.num*10) DAY AS date_val
    FROM 
        (SELECT 0 AS num 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) a,
        (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2) b
    HAVING date_val <= STR_TO_DATE('17-03-2022', '%d-%m-%Y')
) d
LEFT JOIN car_trips c
    ON d.date_val BETWEEN STR_TO_DATE(c.`date of departure`, '%d-%m-%Y') 
                       AND STR_TO_DATE(c.`date of arrival`, '%d-%m-%Y')
GROUP BY d.date_val
ORDER BY d.date_val;

注意事项 #

1. 替换car_trips为你的实际表名。
2. 如果表中日期是DATE类型而非字符串，可去掉STR_TO_DATE转换，直接使用日期字段。
3. 若需动态覆盖所有出行日期区间，可将起始/结束日期替换为子查询：
   • 起始日期：(SELECT MIN(STR_TO_DATE(date of departure, '%d-%m-%Y')) FROM car_trips)
   • 结束日期：(SELECT MAX(STR_TO_DATE(date of arrival, '%d-%m-%Y')) FROM car_trips)

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