SQL查询仅返回单条结果：按产品组计算日期差值需求排查

原始数据表 #

需求说明 #

按Product、Plant、Store分组计算DaysDiff：

• 若分组内存在Stock < 0的记录，取第一条负库存对应的Date，与分组内Week=0的Date计算天数差；
• 若分组内无负库存，取分组内Week=0的Date与Week=2的Date计算天数差。

预期结果：

问题重现 #

用户编写的SQL语句：

SELECT
    Product,
    Plant,
    Store,
    CASE
        WHEN MIN(CASE WHEN Stock < 0 THEN Date END) IS NOT NULL THEN
            DATEDIFF(WEEK, 
                MIN(CASE WHEN Stock < 0 THEN Date END),
                MIN(CASE WHEN Week = 0 THEN Date END)
            )
        ELSE
            DATEDIFF(WEEK, 
                MAX(CASE WHEN Week = 0 THEN Date END),
                MIN(Date)
            )
    END AS DaysDiff
FROM Table
GROUP BY
    Product,
    Plant,
    Store
ORDER BY Product, Plant, Store;

执行后仅返回一条结果，无法得到预期的三个分组结果。

问题分析与修正 #

核心问题 #

1. 表名冲突：Table是SQL关键字，直接使用会引发语法错误或逻辑异常，需替换为实际表名；
2. DATEDIFF参数错误：原SQL使用WEEK单位计算天数差，且参数顺序颠倒，需求需要的是天数差，应使用DAY单位，且参数顺序为(单位, 起始日期, 结束日期)；
3. 无负库存分支逻辑错误：原SQL用MIN(Date)取分组最早日期，不符合需求中取Week=2日期的要求；
4. 分组聚合异常：若未正确替换表名，可能导致数据库无法识别正确的分组数据源。

修正后的SQL #

SELECT
    Product,
    Plant,
    Store,
    CASE
        -- 判断分组是否存在负库存
        WHEN COUNT(CASE WHEN Stock < 0 THEN 1 END) > 0 THEN
            -- 计算第一条负库存日期与Week0日期的天数差
            DATEDIFF(DAY,
                MAX(CASE WHEN Week = 0 THEN Date END),
                MIN(CASE WHEN Stock < 0 THEN Date END)
            )
        ELSE
            -- 无负库存时，计算Week0与Week2的天数差
            DATEDIFF(DAY,
                MAX(CASE WHEN Week = 0 THEN Date END),
                MAX(CASE WHEN Week = 2 THEN Date END)
            )
    END AS DaysDiff
FROM stock_table  -- 替换为你的实际表名
GROUP BY Product, Plant, Store
ORDER BY Product, Plant, Store;

验证说明 #

• 123456分组：第一条负库存日期与Week0日期均为2001-01-01，天数差为0；
• 789123分组：第一条负库存日期2001-01-08与Week0日期2001-01-01的天数差为7；
• 013579分组：无负库存，Week0日期2001-01-01与Week2日期2001-01-16的天数差为15，完全符合预期。

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