使用LEFT JOIN时如何对统计值执行数学运算？

先修正原SQL的错误 #

你的原SQL最后一个LEFT JOIN的关联条件写错了，应该将：

ON PupDetails.txtSchoolID = Absent.txtSchoolID

改为：

ON PupDetails.txtSchoolID = NotReg.txtSchoolID

否则notRegCount会错误关联到缺勤数据，导致统计结果不准确。

添加数学运算的方法 #

要对统计值执行数学运算（比如到场数除以缺勤数），直接在最外层的SELECT中计算即可，但必须处理除数为0的情况，避免报错。同时，为了得到精确的小数结果，需要将整数类型转换为浮点型。

优化后的高效写法 #

原SQL多次关联同一个视图，性能较低，推荐用条件聚合一次性统计三种状态的数量，代码更简洁高效：

SELECT
    PupDetails.txtFullName,
    PupDetails.txtSchoolID,
    -- 将NULL转为0，确保统计值为整数
    ISNULL(Stats.presCount, 0) AS presCount,
    ISNULL(Stats.absentCount, 0) AS absentCount,
    ISNULL(Stats.notRegCount, 0) AS notRegCount,
    -- 到场数/缺勤数：处理除数为0的情况，返回NULL而非报错
    CASE 
        WHEN ISNULL(Stats.absentCount, 0) = 0 THEN NULL
        ELSE CAST(ISNULL(Stats.presCount, 0) AS FLOAT) / ISNULL(Stats.absentCount, 0)
    END AS PresentToAbsentRatio,
    -- 示例：计算到场率（到场数/总记录数）
    CASE 
        WHEN (ISNULL(Stats.presCount,0) + ISNULL(Stats.absentCount,0) + ISNULL(Stats.notRegCount,0)) = 0 THEN NULL
        ELSE CAST(ISNULL(Stats.presCount,0) AS FLOAT) / (ISNULL(Stats.presCount,0) + ISNULL(Stats.absentCount,0) + ISNULL(Stats.notRegCount,0))
    END AS PresentRate
FROM (
    SELECT txtFullName, txtSchoolID
    FROM dbo.TblPupilManagementPupils
    WHERE intSystemStatus = 1
) AS PupDetails
LEFT JOIN (
    SELECT
        txtSchoolID,
        -- 条件聚合：统计各状态的记录数
        COUNT(CASE WHEN txtRegistrationStatus = 'Present' THEN 1 END) AS presCount,
        COUNT(CASE WHEN txtRegistrationStatus = 'Absent' THEN 1 END) AS absentCount,
        COUNT(CASE WHEN txtRegistrationStatus = 'Not Registered' THEN 1 END) AS notRegCount
    FROM dbo.VwRegistrationSchoolRegistrationAttendance
    WHERE dtRegistrationDateTime > '2024-12-03 08:25:00' 
      AND dtRegistrationDateTime < '2024-12-03 16:00:00'
    GROUP BY txtSchoolID
) AS Stats ON PupDetails.txtSchoolID = Stats.txtSchoolID
ORDER BY PupDetails.txtFullName

关键说明 #

• 条件聚合：通过CASE在一次分组查询中完成三种状态的统计，避免多次关联同一个视图，大幅提升查询性能。
• NULL处理：用ISNULL将无记录的NULL值转为0，确保统计值符合预期。
• 除法安全：用CASE判断除数是否为0，避免出现除以0的运行时错误；通过CAST转换数据类型，得到精确的小数结果。

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