Firebird 2.5中查询指定日期区间内每日奖励统计（含无数据日期）

你的需求是查询Firebird 2.5数据库中，指定名称（比如Chris）在2018-05-04至2018-05-08日期区间内，每日REWARD为"yes"的数量，而且没有奖励记录的日期要显示AMOUNT为0。

先看你的现有MyTable数据：

期望得到的结果是：

你之前尝试的SQL之所以不行，是因为它只从MyTable里筛选存在的记录，完全忽略了区间内没有数据的日期：

SELECT name, SUM(CASE WHEN reward='yes' THEN 1 ELSE 0 END) AS AMOUNT, DATE 
from MyTable 
WHERE DATE between '04.05.2018' and '08.05.2018' AND NAME='Chris' 
GROUP BY NAME, DATE

正确的解决方案 #

Firebird 2.5支持递归CTE（WITH RECURSIVE），我们可以用它先生成目标日期区间的所有日期，再通过左连接关联原表做统计，这样就能把空日期的0值也展示出来：

WITH RECURSIVE date_range AS (
    -- 起始日期，用RDB$DATABASE生成单条记录（Firebird的小技巧）
    SELECT CAST('2018-05-04' AS DATE) AS target_date
    FROM RDB$DATABASE
    UNION ALL
    -- 递归生成后续日期，直到达到结束日期
    SELECT target_date + 1
    FROM date_range
    WHERE target_date < CAST('2018-05-08' AS DATE)
)
SELECT 
    'Chris' AS NAME,
    -- 用COALESCE把NULL转成0，确保空日期显示0
    COALESCE(SUM(CASE WHEN t.REWARD = 'yes' THEN 1 ELSE 0 END), 0) AS AMOUNT,
    -- 把日期格式化成你要的dd.mm.yyyy样式
    TO_CHAR(d.target_date, 'DD.MM.YYYY') AS DATE
FROM date_range d
-- 左连接确保所有生成的日期都保留，哪怕原表没数据
LEFT JOIN MyTable t 
    ON d.target_date = CAST(t.DATE AS DATE) 
    AND t.NAME = 'Chris'
GROUP BY d.target_date
ORDER BY d.target_date;

几个关键细节： #

• 递归生成日期序列：date_range这个CTE从起始日期开始，每天加1，直到覆盖整个目标区间，这样就不会漏掉任何一天。
• 左连接的作用：LEFT JOIN保证了即使原表中某一天没有Chris的记录，这一天也会出现在结果里，而不是被过滤掉。
• 处理NULL值：当某一天没有匹配的记录时，SUM()会返回NULL，用COALESCE把它转换成0，正好符合你的需求。
• 日期格式：用TO_CHAR()把日期转换成DD.MM.YYYY格式，如果你的MyTable里的DATE字段已经是DATE类型，那CAST(t.DATE AS DATE)可以去掉，直接用t.DATE就行。

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