SQL整数除法致查询返回0行，求效率百分比计算问题解决方案

核心问题原因 #

1. 除数为0的隐性过滤：当Calculated_Shouldhavetaken的值为0时，除法运算会触发数据库错误，导致对应行被过滤，甚至直接返回空结果集——这是仅计算差值时能正常返回数据，添加除法后返回0行的主要原因。
2. SELECT子句别名引用限制：多数SQL数据库不允许在同一SELECT子句内直接引用前面定义的字段别名（比如直接用Shouldhavetaken计算效率值），这种写法可能引发未定义行为。

具体修复步骤 #

1. 处理除数为0的情况 #

使用NULLIF函数将0转换为NULL避免报错，或用CASE语句定义业务所需的特殊值（如0）。

2. 避免直接引用SELECT内的别名 #

通过CTE（公共表表达式）、子查询复用计算逻辑，或直接将Calculated_Shouldhavetaken的计算式代入效率百分比的计算中。

修改后的查询示例 #

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
WITH LaborCalculations AS (
    select 
        [LaborDtl].[JobNum] as [LaborDtl_JobNum],
        [JobHead].[PartNum] as [JobHead_PartNum],
        [LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
        [LaborDtl].[OpCode] as [LaborDtl_OpCode],
        [JobHead].[ProdQty] as [JobHead_ProdQty],
        (LaborDtl.LaborQty + LaborDtl.ScrapQty) as [Calculated_TotalQty],
        [LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
        (TotalQty/ JobHead.ProdQty* LaborDtl.LaborHrs* 1.0) as [Calculated_Shouldhavetaken],
        [LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate]
    from Erp.LaborDtl as LaborDtl
    inner join Erp.JobOper as JobOper on 
        LaborDtl.Company = JobOper.Company
        and LaborDtl.JobNum = JobOper.JobNum
        and LaborDtl.AssemblySeq = JobOper.AssemblySeq
        and LaborDtl.OprSeq = JobOper.OprSeq
    inner join Erp.JobOpDtl as JobOpDtl on 
        JobOper.Company = JobOpDtl.Company
        and JobOper.JobNum = JobOpDtl.JobNum
        and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
        and JobOper.OprSeq = JobOpDtl.OprSeq
    inner join Erp.JobHead as JobHead on 
        JobOpDtl.Company = JobHead.Company
        and JobOpDtl.JobNum = JobHead.JobNum
    where (LaborDtl.ClockInDate = dateadd (day, -1, Constants.Today))
)
SELECT
    *,
    -- 除数为0时返回NULL，可根据业务改为0或其他值
    ((LaborDtl_LaborHrs - Calculated_Shouldhavetaken) / NULLIF(Calculated_Shouldhavetaken, 0)) as [Calculated_effpercent]
FROM LaborCalculations;

额外注意事项 #

• 你已通过*1.0确保除法为浮点运算，这一步能避免整数除法导致的结果失真，建议保留。
• 若业务要求除数为0时返回特定值，可替换为CASE WHEN Calculated_Shouldhavetaken = 0 THEN 0 ELSE ((LaborDtl_LaborHrs - Calculated_Shouldhavetaken)/Calculated_Shouldhavetaken) END。

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