如何构造满足日期与品种类型过滤规则的历史数据SQL查询

正确查询写法 #

SELECT SUM(NVL(sales, 0)) AS sales
FROM results t
WHERE t.race_date BETWEEN '12/30/2020' AND '01/02/2021'
AND (
    (t.race_date < DATE '2021-01-01' AND t.breed_type IN (1,2))
    OR
    (t.race_date >= DATE '2021-01-01' AND t.breed_type IN (1,2,3))
);

原查询错误原因 #

原写法没有按日期区分breed_type的过滤规则，直接将所有日期下breed_type=3的数据都纳入统计，多算了2020年12月30日breed_type=3的40、2020年12月31日breed_type=3的10，合计多算50，185减去50正好匹配你预期的135结果。

可选写法（CASE WHEN实现，可读性更高） #

如果你的数据库支持CASE WHEN语法，也可以用如下方式实现相同逻辑：

SELECT SUM(
    CASE
        WHEN t.race_date < DATE '2021-01-01' AND t.breed_type = 3 THEN 0
        ELSE NVL(sales, 0)
    END
) AS sales
FROM results t
WHERE t.race_date BETWEEN '12/30/2020' AND '01/02/2021'
AND t.breed_type IN (1,2,3);

注意事项 #

建议统一使用YYYY-MM-DD格式的标准日期写法，避免不同数据库对MM/DD/YYYY格式的解析差异导致过滤错误。

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