sql-server - T-SQL 聚合函数子查询

Question

我收到以下错误:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

使用这段代码:

SELECT 
    loc.Location
    ,COUNT(CASE 
        WHEN hr.SAC in (SELECT [SAC] FROM dbo.[Titles] WHERE [title] = 'XYZ')
            THEN 1 
            ELSE NULL 
        END) AS XYZ_Trainee_Count
    ,COUNT(CASE 
        WHEN hr.SAC in (SELECT [SAC] FROM dbo.[Titles] WHERE [title] = 'ABC')
            THEN 1 
            ELSE NULL 
        END) AS ABC_Trainee_Count

FROM
    dbo.n_HRODS hr INNER JOIN dbo.Locations loc
        ON loc.LocationID = hr.LocationID
    INNER JOIN dbo.EmpData dat
        ON dat.EmpID = hr.EmpID

WHERE dat.Trainee = 1

GROUP BY loc.Location

dbo.[Titles] 是一个 View ，它结合了来自其他两个表的两列。我基本上是这样做的，因为我之前的程序员做了这样的事情:

,COUNT(CASE 
    WHEN SAC in ( lists about 30 items)
        THEN 1 
        ELSE NULL 
    END) 

显然，我不想在该案例陈述中列出 30 个项目……当这些项目在 3 年内因任何原因发生变化时，那么谁会记得返回此代码并更新这些项目？没有人...

预先感谢您的帮助。

Answer 1

你可以用几个额外的 LEFT OUTER JOINs 到那个 title 表来做到这一点:

SELECT 
    loc.Location
    ,COUNT(CASE WHEN titles1.[SAC] IS NOT NULL THEN 1 ELSE NULL END) AS XYZ_Trainee_Count
    ,COUNT(CASE WHEN titles2.[SAC] IS NOT NULL THEN 1 ELSE NULL END) AS ABC_Trainee_Count

FROM
    dbo.n_HRODS hr INNER JOIN dbo.Locations loc
        ON loc.LocationID = hr.LocationID
    INNER JOIN dbo.EmpData dat
        ON dat.EmpID = hr.EmpID
    LEFT OUTER JOIN dbo.[Titles] titles1
        ON titles1.[title]='XYZ' AND
            hr.SAC = titles1.[SAC]
    LEFT OUTER JOIN dbo.[Titles] titles2
        ON titles2.[title]='ABC' AND
            hr.sac = titles2.[SAC]    
WHERE dat.Trainee = 1    
GROUP BY loc.Location

或者，如果您真的与 SELECT 语句中的那些子查询结合在一起，因为实际的查询是一场大噩梦，并且对连接进行修改的想法足以让您晕倒，那么您可以从此查询中删除聚合并在聚合之前将其全部推送到子查询中:

SELECT location, count(XYZ_Trainee) AS XYZ_Trainee_Count, count(ABC_Trainee) as ABC_Trainee
FROM
(
    SELECT 
        loc.Location
        ,CASE 
            WHEN hr.SAC in (SELECT [SAC] FROM dbo.[Titles] WHERE [title] = 'XYZ')
                THEN 1 
                ELSE NULL 
            END AS XYZ_Trainee
        ,CASE 
            WHEN hr.SAC in (SELECT [SAC] FROM dbo.[Titles] WHERE [title] = 'ABC')
                THEN 1 
                ELSE NULL 
            END AS ABC_Trainee

    FROM
        dbo.n_HRODS hr INNER JOIN dbo.Locations loc
            ON loc.LocationID = hr.LocationID
        INNER JOIN dbo.EmpData dat
            ON dat.EmpID = hr.EmpID

    WHERE dat.Trainee = 1
) sub
GROUP BY location

不过，我的目标是第一个解决方案，因为它更易于维护，并且可能从 RDBMS 获得更好的执行路径并因此运行得更快。虽然……这只是一个猜测。