为何嵌套查询中除法分母增大时，符合条件的记录数反而增加？

我需要计算员工总加班时长，逻辑是：用基本工资除以月工时（比如173）得到时薪，再用员工加班总金额除以时薪。但发现一个反常情况——当月工时数值增大时，符合「加班时长>40」条件的记录数反而增加，和预期的减少相反。以下是我的SQL脚本：

select 'Employees that worked more overtime' [CAAT],*
    from (  select H.[Month]
            ,H.[Employee Code]
            ,H.[Department]
            ,H.[Job title]
            ,H.[Surname]
            ,H.[Full Names]
            ,H.[Basic Salary]
            ,R.[Overtime]
            ,H.[Hourly Rate]
            ,round(R.[Overtime] / H.[Hourly Rate],2) [Overtime Hours]
            from (select [Month]
                        ,[Employee Code]
                        ,Department
                        ,[Job title]
                        ,[Surname]
                        ,[Full Names]
                        ,nullif(convert(money,[Amount]),0.00) [Basic Salary]
                        ,nullif(round(convert(money,[Amount]) / 173,2),0.00) [Hourly Rate] 
                            from [Salary DB] 
                                where [Field Desc] = 'ED01-Basic Salary') H

                left join 
                                
                (select [Month]
                        ,[Employee Code]
                        ,nullif(sum(convert(money,[Amount])),0.00) [Overtime] 
                        from [Salary DB]
                            where [Field Desc] in ('ED02-O/Time 1.5','ED02-O/Time 2.0','ED42-Sunday Pay') 
                                group by [Month]
                                        ,[Employee Code]) R 
                                    
                            on H.[Employee Code] = R.[Employee Code]
                                and H.[Month] = R.[Month]) [Data]
                                            
    where [Overtime Hours] > '40'
        Order by [Employee Code], [Month] Desc

问题原因 #

1. 逻辑层面的预期偏差：
   月工时增大时，时薪（基本工资/月工时）会变小。而加班时长=加班总金额/时薪，时薪越小，计算出的加班时长就越大。原本加班时长可能略低于40的记录，会因为时薪变小而超过40，导致符合条件的记录数增加，这是数学逻辑的必然结果，并非SQL错误。

2. SQL代码的潜在问题：

   • 条件判断[Overtime Hours] > '40'中，将数值型的加班时长与字符串'40'比较，会触发隐式类型转换，可能导致意想不到的结果，应改为与数值40比较。
   • 计算时薪时使用round(...,2)会截断精度，可能导致加班时长的计算出现误差，若不需要四舍五入可去掉该函数，或根据业务需求调整精度处理方式。

修正后的SQL脚本 #

select 'Employees that worked more overtime' [CAAT],*
from (
    select 
        H.[Month],
        H.[Employee Code],
        H.[Department],
        H.[Job title],
        H.[Surname],
        H.[Full Names],
        H.[Basic Salary],
        R.[Overtime],
        H.[Hourly Rate],
        round(R.[Overtime] / H.[Hourly Rate], 2) [Overtime Hours]
    from (
        select 
            [Month],
            [Employee Code],
            Department,
            [Job title],
            [Surname],
            [Full Names],
            nullif(convert(money, [Amount]), 0.00) [Basic Salary],
            -- 若不需要四舍五入时薪，可去掉round函数
            nullif(convert(money, [Amount]) / 173, 0.00) [Hourly Rate] 
        from [Salary DB] 
        where [Field Desc] = 'ED01-Basic Salary'
    ) H
    left join (
        select 
            [Month],
            [Employee Code],
            nullif(sum(convert(money, [Amount])), 0.00) [Overtime] 
        from [Salary DB]
        where [Field Desc] in ('ED02-O/Time 1.5','ED02-O/Time 2.0','ED42-Sunday Pay') 
        group by [Month], [Employee Code]
    ) R on H.[Employee Code] = R.[Employee Code] and H.[Month] = R.[Month]
) [Data]
where [Overtime Hours] > 40  -- 改为数值比较
order by [Employee Code], [Month] Desc

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