数据库查询疑问：如何使指定日期区间的统计查询符合预期输出？

你的原始查询仅会返回tbl表中存在订单记录的日期，若要让1-4号所有日期都显示（无订单时对应Total orders显示0），需要先生成包含目标日期的完整序列，再与原查询结果做左关联，最后将空值替换为0。以下是不同数据库的实现方式：

1. MySQL/MariaDB #

WITH dates AS (
    SELECT 1 AS date UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
)
SELECT 
    d.date,
    COALESCE(sum(t.orders), 0) AS "Total orders"
FROM dates d
LEFT JOIN tbl t ON d.date = t.date
WHERE d.date >= 1 AND d.date <= 4
GROUP BY d.date
ORDER BY d.date;

2. PostgreSQL #

WITH dates AS (
    SELECT generate_series(1,4) AS date
)
SELECT 
    d.date,
    COALESCE(sum(t.orders), 0) AS "Total orders"
FROM dates d
LEFT JOIN tbl t ON d.date = t.date
GROUP BY d.date
ORDER BY d.date;

3. SQL Server #

WITH dates AS (
    SELECT n AS date
    FROM (VALUES(1),(2),(3),(4)) AS nums(n)
)
SELECT 
    d.date,
    COALESCE(sum(t.orders), 0) AS "Total orders"
FROM dates d
LEFT JOIN tbl t ON d.date = t.date
GROUP BY d.date
ORDER BY d.date;

核心逻辑说明 #

• 用CTE（WITH子句）生成包含1到4号的完整日期列表，确保所有目标日期都被包含。
• 通过LEFT JOIN关联原表tbl，保留所有生成的日期，即使原表中无对应记录。
• 使用COALESCE函数将无订单日期对应的sum(t.orders)空值替换为0。
• 按日期排序保证输出顺序符合预期。

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