如何在嵌套SQL查询中添加条件，日期数不等时子查询返回Null？

可以通过先计算两个日期区间的去重日期数量，再用条件判断控制旧区间售车数的返回值，具体SQL如下：

SELECT 
    sum(CARS_SOLD) as cars_sold,
    CASE 
        WHEN (SELECT COUNT(DISTINCT date(date_sold)) FROM <DB> WHERE date(date_sold) BETWEEN '2023-01-21' AND '2023-01-31') 
             = (SELECT COUNT(DISTINCT date(date_sold)) FROM <DB> WHERE date(date_sold) BETWEEN '2023-02-01' AND '2023-02-11')
        THEN (SELECT sum(CARS_SOLD) FROM <DB> WHERE date(date_sold) BETWEEN '2023-01-21' AND '2023-01-31')
        ELSE NULL
    END as cars_sold_old
FROM <DB>
WHERE date(date_sold) BETWEEN '2023-02-01' AND '2023-02-11'

逻辑说明 #

• 两个子查询分别统计两个目标区间内的去重日期数量
• 用CASE WHEN做判断：如果两个区间的去重日期数相等，返回旧区间的售车总数；否则返回NULL
• 外层查询正常统计新区间的售车总数

如果想减少重复扫描数据表的次数，用CTE（公共表表达式）优化更高效：

WITH date_counts AS (
    SELECT 
        COUNT(DISTINCT CASE WHEN date(date_sold) BETWEEN '2023-01-21' AND '2023-01-31' THEN date(date_sold) END) AS old_days,
        COUNT(DISTINCT CASE WHEN date(date_sold) BETWEEN '2023-02-01' AND '2023-02-11' THEN date(date_sold) END) AS new_days,
        SUM(CASE WHEN date(date_sold) BETWEEN '2023-01-21' AND '2023-01-31' THEN CARS_SOLD END) AS old_sum,
        SUM(CASE WHEN date(date_sold) BETWEEN '2023-02-01' AND '2023-02-11' THEN CARS_SOLD END) AS new_sum
    FROM <DB>
)
SELECT 
    new_sum AS cars_sold,
    CASE WHEN old_days = new_days THEN old_sum ELSE NULL END AS cars_sold_old
FROM date_counts

优化说明 #

• CTE只扫描一次数据表，同时算出两个区间的去重日期数和售车总数
• 后续直接基于CTE结果做判断和返回，数据量大时性能提升明显

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