SQL查询报Subquery returned more than 1 value错误修复

报错Subquery returned more than 1 value的触发逻辑是：被当作单值表达式使用的子查询返回了多行结果，SQL引擎无法将多行结果作为单个字段值输出。
这段代码中触发报错的核心位置是计算plannable_factor的子查询：

(SELECT 1 - isnull(ec.TT_PERCNOTPLAN, 0)/100
  FROM TT_EMP_CONTRACT ec
  WHERE ec.TT_EMP_ID = e.TT_EMP_ID
    AND ec.TT_TODATE >= GETDATE() ) AS plannable_factor

当同一个员工存在多份有效期覆盖当前日期的合同记录时，这个子查询就会返回多行结果，直接触发报错。
此外原SQL大量使用关联子查询做逐行计算，不仅容易触发这类单行约束错误，执行效率也偏低，可一并优化。

• 对plannable_factor子查询增加TOP 1限制，按合同生效日期倒序取最新的有效合同，从根源避免子查询返回多行；同时增加空值默认值，无有效合同时默认可排班比例为100%
• 对工时计算子查询增加空值处理，无排班记录时默认工时为0，避免NULL值导致计算结果异常
• 把最终查询中两个按组织维度聚合工时的关联子查询，提前通过CTE做预聚合，再通过左关联匹配结果，减少全表重复扫描，提升执行效率

WITH emp_orgs AS
(
    SELECT 
        e.TT_EMP_ID,
        soi.TT_ITEM AS Hoofdorganisatie
    FROM tt_emp e
    LEFT JOIN TT_VW_LABEL_EMP vle 
        ON e.TT_EMP_ID = vle.TT_DIM_ID
    LEFT JOIN TT_SYS_OPT_ITM soi 
        ON soi.TT_ITEM_ID = vle.TT_ITEM_ID
        AND soi.TT_OPT_ID = 5
        AND soi.TT_CODE = 'TECH'
),
raster_hours_next_6_months AS
(
    SELECT 
        e.TT_EMP_ID,
        ISNULL((
            SELECT SUM(si.TT_HOURS)
            FROM TT_SYS_DAYS sd
            LEFT JOIN TT_EMP_SCHED es 
                ON es.TT_EMP_ID = e.TT_EMP_ID
                AND es.TT_FROMDATE <= sd.TT_DATE
                AND es.TT_TODATE >= sd.TT_DATE
            LEFT JOIN TT_SCHEDDAY_ITM si 
                ON sd.TT_DATE = si.TT_DATE
                AND si.TT_SCHED_ID = es.TT_SCHED_ID
            WHERE sd.TT_DATE >= GETDATE()
                AND sd.TT_DATE <= DATEADD(MONTH, 6, GETDATE())
        ), 0) AS raster_hours_next_6_months,
        ISNULL((
            SELECT TOP 1 1 - ec.TT_PERCNOTPLAN/100
            FROM TT_EMP_CONTRACT ec
            WHERE ec.TT_EMP_ID = e.TT_EMP_ID
                AND ec.TT_TODATE >= GETDATE()
            ORDER BY ec.TT_FROMDATE DESC, ec.TT_TODATE DESC
        ), 1) AS plannable_factor,
        eo.Hoofdorganisatie
    FROM TT_EMP e
    LEFT JOIN emp_orgs eo 
        ON eo.TT_EMP_ID = e.TT_EMP_ID
    WHERE eo.Hoofdorganisatie IS NOT NULL
),
org_hour_agg AS
(
    SELECT
        Hoofdorganisatie,
        SUM(raster_hours_next_6_months) / 6 AS total_scheduled_hours,
        SUM(raster_hours_next_6_months * plannable_factor) /6 AS total_plannable_hours
    FROM raster_hours_next_6_months
    GROUP BY Hoofdorganisatie
)
SELECT 
    soi.TT_ITEM_ID as CategoryKey,
    ISNULL(oha.total_scheduled_hours, 0) as ScheduledHours,
    ISNULL(oha.total_plannable_hours, 0) as ScheduledHoursPlannable
FROM TT_SYS_OPT_ITM soi
LEFT JOIN org_hour_agg oha
    ON oha.Hoofdorganisatie = soi.TT_ITEM
WHERE soi.TT_OPT_ID = 5
    AND soi.TT_CODE = 'TECH'

如果业务规则要求同一个员工的多份有效合同需要按比例折算可排班系数，把plannable_factor子查询里的TOP 1逻辑改成AVG(1 - ec.TT_PERCNOTPLAN/100)做聚合即可，同样能避免返回多行的问题。

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