按ID汇总WOR_est_total_hours与cub_total_erect_hours列报错求助

问题背景 #

尝试按相同ID对WOR_est_total_hours列和cub_total_erect_hours列的所有行求和时，持续遇到两类错误：

• is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
• Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

关联多张表的原始查询代码如下，同时存在同一ID对应多行工时记录的场景。

原始查询代码 #

SELECT WOR.[id],
   [request_number]               AS [Request Number],
   WOR.[create_date],
   WOR.[modify_date],
   cub.total_erect_hours          AS cub_total_erect_hours,
   COALESCE(mpp.total_hours, 0 )
   + COALESCE(leg.total_hours, 0)
   + COALESCE(cub.total_hours, 0) AS WOR_est_total_hours
FROM   [dbo].[work_order_request] WOR
   LEFT JOIN [dbo].[workorder] WO
          ON WOR.[id] = WO.[wor_id]
   INNER JOIN [dbo].[wor_type] WT
           ON WOR.[wor_type_id] = WT.[id]
   LEFT JOIN [dbo].[status] S
          ON WOR.[workorderrequest_status_id] = S.[id]
   LEFT JOIN [dbo].[work_scope] WS
          ON WOR.[work_class] = WS.[id]
   INNER JOIN [dbo].[workflow] WF
           ON WOR.[workflow_id] = WF.[id]
   LEFT JOIN [dbo].[person] CP
          ON WOR.[contact_user_id] = CP.[id]
   LEFT JOIN [dbo].[person] RP
          ON WOR.[requested_by_user_id] = RP.[id]
   LEFT OUTER JOIN [work_order_systems_concat]
                ON WOR.[id] =
                   [work_order_systems_concat].[work_order_request_id]
   LEFT JOIN [dbo].[project] P
          ON WOR.[project_id] = P.[id]
   LEFT JOIN [dbo].[organization] O
          ON P.[organization_id] = O.[id]
   LEFT JOIN [dbo].[construction_work_package] CWP
          ON WOR.[construction_work_package_id] = CWP.[id]
   LEFT JOIN [dbo].[internal_work_package] IWP
          ON WOR.[internal_work_package] = IWP.[id]
   LEFT JOIN [dbo].[request_comment] RC1
          ON ( WOR.[id] = RC1.[work_order_request_id] )
   LEFT JOIN [dbo].[request_comment] RC2
          ON ( WOR.[id] = RC2.[work_order_request_id]
               AND ( RC1.[create_date] < RC2.[create_date]
                      OR ( RC1.[create_date] = RC2.[create_date]
                           AND RC1.[id] < RC2.[id] ) ) )
   LEFT JOIN dbo.cubic_meter_workorder_request_calculations_est_hours cub
          ON cub.wor_id = wo.wor_id
   LEFT JOIN dbo.leg_footage_workorder_request_calculations_est_hours leg
          ON leg.wor_id = wo.wor_id
   LEFT JOIN dbo.mpp_workorder_request_calculations_est_hours mpp
          ON mpp.wor_id = wo.wor_id
   LEFT JOIN dbo.usergroup ug
          ON ug.id = wor.scaffolding_contractor_usergroup_id
   LEFT JOIN dbo.estimate e
          ON wo.wor_id = e.work_order_request_id
WHERE  WO.[parent_workorder_id] IS NULL
   AND [request_number] != 'Draft'
   AND RC2.[id] IS NULL
   AND e.parent_estimate_id IS NULL
   AND wor.parent_work_order_request_id IS NULL
   AND wor.id IN ( 8293, 8342, 8343, 8344,
                   8396, 8396, 8399 )

数据场景说明 #

数据示例显示：同一WOR.id对应多条cub_total_erect_hours和WOR_est_total_hours记录，需要按ID合并求和。

错误原因分析 #

1. 第一类错误：使用聚合函数（如SUM）时，未将SELECT中的非聚合列加入GROUP BY子句，SQL语法要求所有非聚合列必须在GROUP BY中声明。
2. 第二类错误：直接关联工时计算表（cubic_meter_workorder_request_calculations_est_hours等）时，一个wor_id对应多条记录，导致主表行被重复展开，后续若用子查询取单值会返回多行触发错误。

核心问题是：子表存在一对多关联，直接关联会产生重复行，导致求和逻辑混乱。需先对这些子表按wor_id聚合求和，再关联主表。

修正后的查询代码 #

SELECT 
    WOR.[id],
    [request_number] AS [Request Number],
    WOR.[create_date],
    WOR.[modify_date],
    -- 取聚合后的cub总安装工时
    COALESCE(cub.sum_total_erect_hours, 0) AS cub_total_erect_hours,
    -- 计算总预估工时
    COALESCE(mpp.sum_total_hours, 0)
    + COALESCE(leg.sum_total_hours, 0)
    + COALESCE(cub.sum_total_hours, 0) AS WOR_est_total_hours
FROM [dbo].[work_order_request] WOR
LEFT JOIN [dbo].[workorder] WO
    ON WOR.[id] = WO.[wor_id]
INNER JOIN [dbo].[wor_type] WT
    ON WOR.[wor_type_id] = WT.[id]
LEFT JOIN [dbo].[status] S
    ON WOR.[workorderrequest_status_id] = S.[id]
LEFT JOIN [dbo].[work_scope] WS
    ON WOR.[work_class] = WS.[id]
INNER JOIN [dbo].[workflow] WF
    ON WOR.[workflow_id] = WF.[id]
LEFT JOIN [dbo].[person] CP
    ON WOR.[contact_user_id] = CP.[id]
LEFT JOIN [dbo].[person] RP
    ON WOR.[requested_by_user_id] = RP.[id]
LEFT OUTER JOIN [work_order_systems_concat]
    ON WOR.[id] = [work_order_systems_concat].[work_order_request_id]
LEFT JOIN [dbo].[project] P
    ON WOR.[project_id] = P.[id]
LEFT JOIN [dbo].[organization] O
    ON P.[organization_id] = O.[id]
LEFT JOIN [dbo].[construction_work_package] CWP
    ON WOR.[construction_work_package_id] = CWP.[id]
LEFT JOIN [dbo].[internal_work_package] IWP
    ON WOR.[internal_work_package] = IWP.[id]
LEFT JOIN [dbo].[request_comment] RC1
    ON WOR.[id] = RC1.[work_order_request_id]
LEFT JOIN [dbo].[request_comment] RC2
    ON WOR.[id] = RC2.[work_order_request_id]
        AND (RC1.[create_date] < RC2.[create_date]
             OR (RC1.[create_date] = RC2.[create_date] AND RC1.[id] < RC2.[id]))
-- 先对cub表按wor_id聚合求和
LEFT JOIN (
    SELECT wor_id,
           SUM(total_erect_hours) AS sum_total_erect_hours,
           SUM(total_hours) AS sum_total_hours
    FROM dbo.cubic_meter_workorder_request_calculations_est_hours
    GROUP BY wor_id
) cub ON cub.wor_id = wo.wor_id
-- 先对leg表按wor_id聚合求和
LEFT JOIN (
    SELECT wor_id,
           SUM(total_hours) AS sum_total_hours
    FROM dbo.leg_footage_workorder_request_calculations_est_hours
    GROUP BY wor_id
) leg ON leg.wor_id = wo.wor_id
-- 先对mpp表按wor_id聚合求和
LEFT JOIN (
    SELECT wor_id,
           SUM(total_hours) AS sum_total_hours
    FROM dbo.mpp_workorder_request_calculations_est_hours
    GROUP BY wor_id
) mpp ON mpp.wor_id = wo.wor_id
LEFT JOIN dbo.usergroup ug
    ON ug.id = wor.scaffolding_contractor_usergroup_id
LEFT JOIN dbo.estimate e
    ON wo.wor_id = e.work_order_request_id
WHERE WO.[parent_workorder_id] IS NULL
    AND [request_number] != 'Draft'
    AND RC2.[id] IS NULL
    AND e.parent_estimate_id IS NULL
    AND wor.parent_work_order_request_id IS NULL
    AND wor.id IN (8293, 8342, 8343, 8344, 8396, 8399)
-- 若主表同一ID对应多条记录，需按ID汇总则取消注释以下内容
/*
GROUP BY WOR.[id], [request_number], WOR.[create_date], WOR.[modify_date]
*/

补充说明 #

• 子表先聚合再关联的方式，从根源避免了行重复问题，同时符合SQL聚合语法规则，解决了两类错误。
• 如果主表中同一WOR.id对应多条记录，需要最终按ID合并所有行的工时，取消注释最后的GROUP BY子句，并根据业务需求对非工时列选择合适的聚合函数（比如MAX([request_number])）。

内容的提问来源于stack exchange，提问作者ATL-JP