SQL查询异常：自定义YIS变量的理赔单数量求和错误

问题描述 #

需要按销售月份SalesDt（格式如202102）统计理赔单数量，同时基于UsePeriod（部件故障天数）计算自定义变量YIS（规则：UsePeriod除以365后取整加1，格式为nYIS），按YIS拆分统计对应理赔单数量。但现有SQL执行结果不符合预期，无法定位求和错误原因。

原SQL代码：

SELECT CONCAT(datepart (yy,SalesDt), FORMAT (SalesDt,'MM')) AS 'SalesYM'
    ,SUM(
        CASE WHEN CONCAT(STR(CEILING(UsePeriod/365)+1),'YIS') = '1YIS' 
        THEN 1 
        ELSE 0 
        END) AS '1YIS'
    ,SUM(
       CASE WHEN CONCAT(STR(CEILING(UsePeriod/365)+1),'YIS') = '2YIS' 
       THEN 1 
       ELSE 0 
       END) AS '2YIS'
FROM [Claim] AS C
LEFT JOIN [Type] AS T
ON C.Code = T.Code
WHERE T.Prod='TR'
GROUP BY CONCAT(datepart (yy,SalesDt), FORMAT (SalesDt,'MM'))
ORDER BY CONCAT(datepart (yy,SalesDt), FORMAT (SalesDt,'MM'))

错误原因分析 #

1. STR函数的前导空格问题：STR()函数默认返回带前导空格的字符串（比如STR(1)返回' 1'而非'1'），导致CONCAT(STR(...), 'YIS')生成' 1YIS'，和条件中的'1YIS'不匹配，最终SUM统计结果错误。
2. 整数除法逻辑偏差：如果UsePeriod是整数类型，UsePeriod/365会执行整数除法（比如365/365=1，364/365=0），若需精确计算小数部分后取整，应使用UsePeriod/365.0触发浮点除法。
3. LEFT JOIN被隐式转为INNER JOIN：WHERE T.Prod='TR'会过滤掉Type表中无匹配的理赔单（此时T.Prod为NULL），若需保留所有理赔单（即使无Type匹配），应将该条件移至ON子句。

修正后的SQL代码 #

方案1：修正格式与除法逻辑（保持INNER JOIN效果） #

SELECT 
    CONCAT(DATEPART(yy, SalesDt), FORMAT(SalesDt, 'MM')) AS SalesYM,
    SUM(CASE WHEN CEILING(UsePeriod / 365.0) + 1 = 1 THEN 1 ELSE 0 END) AS [1YIS],
    SUM(CASE WHEN CEILING(UsePeriod / 365.0) + 1 = 2 THEN 1 ELSE 0 END) AS [2YIS]
FROM [Claim] AS C
JOIN [Type] AS T
    ON C.Code = T.Code
WHERE T.Prod = 'TR'
GROUP BY CONCAT(DATEPART(yy, SalesDt), FORMAT(SalesDt, 'MM'))
ORDER BY SalesYM

方案2：保留LEFT JOIN并调整条件位置 #

如果需要包含Claim表中无对应Type记录的行，可调整为：

SELECT 
    CONCAT(DATEPART(yy, SalesDt), FORMAT(SalesDt, 'MM')) AS SalesYM,
    SUM(CASE WHEN CEILING(UsePeriod / 365.0) + 1 = 1 THEN 1 ELSE 0 END) AS [1YIS],
    SUM(CASE WHEN CEILING(UsePeriod / 365.0) + 1 = 2 THEN 1 ELSE 0 END) AS [2YIS]
FROM [Claim] AS C
LEFT JOIN [Type] AS T
    ON C.Code = T.Code AND T.Prod = 'TR'
GROUP BY CONCAT(DATEPART(yy, SalesDt), FORMAT(SalesDt, 'MM'))
ORDER BY SalesYM

关键优化点说明 #

• 直接比较数值而非拼接字符串，避免STR()函数的格式问题，同时提升查询效率。
• 使用365.0触发浮点除法，确保UsePeriod的小数部分被正确计算后再取整。
• 明确JOIN类型（INNER/LEFT），根据业务需求调整条件位置，避免逻辑偏差。

内容的提问来源于stack exchange，提问作者Rafael Rodrigues Santos