SQL Server“无法对包含聚合或子查询的表达式执行聚合函数”，但 Sybase 可以
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15751241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server "cannot perform an aggregate function on an expression containing an aggregate or a subquery", but Sybase can
提问by PillowMetal
This issue has been discussed before, but none of the answers address my specific problem because I am dealing with different where clauses in the inner and outer selects. This query executed just fine under Sybase, but gives the error in the title of this post when executed under SQL Server. The query is complicated, but the general outline of the query is:
这个问题之前已经讨论过，但没有一个答案能解决我的具体问题，因为我在内部和外部选择中处理不同的 where 子句。这个查询在 Sybase 下执行得很好，但是在 SQL Server 下执行时，在这篇文章的标题中给出了错误。查询很复杂，但查询的大纲是：
select sum ( t.graduates -
( select sum ( t1.graduates )
from table as t1
where t1.id = t.id and t1.group_code not in ('total', 'others' ) ) )
from table as t
where t.group_code = 'total'
The following describes the situation I am trying to resolve:
以下描述了我正在尝试解决的情况：
- all group codes represent races except for 'total' and 'others'
- group code 'total' represents the total graduates of all races
- however, multi-race is missing, so the race graduate counts may not add up to the total graduate counts
- this missing data is what needs to be calculated
- 所有组代码都代表种族，除了“总”和“其他”
- 组码'total'代表所有种族的毕业生总数
- 但是，缺少多种族，因此种族毕业生人数可能不会加到总毕业生人数中
- 这个缺失的数据是需要计算的
Is there anyway to rewrite this using derived tables or joins to get the same results?
无论如何使用派生表或连接重写它以获得相同的结果？
Update:I created sample data and 3 solutions to my specific problem(2 influenced by sgeddes). The one that I added involves moving the correlated subquery to a derived table in the FROM clause. Thanks for the help guys!
更新：我为我的特定问题创建了示例数据和 3 个解决方案（2 个受 sgeddes 影响）。我添加的一个涉及将相关子查询移动到 FROM 子句中的派生表。感谢您的帮助！
回答by sgeddes
One option is to put the subquery in a LEFT JOIN:
一种选择是将子查询放在 a 中LEFT JOIN：
select sum ( t.graduates ) - t1.summedGraduates
from table as t
left join
(
select sum ( graduates ) summedGraduates, id
from table
where group_code not in ('total', 'others' )
group by id
) t1 on t.id = t1.id
where t.group_code = 'total'
group by t1.summedGraduates
Perhaps a better option would be to use SUMwith CASE:
也许更好的选择是使用SUMwith CASE：
select sum(case when group_code = 'total' then graduates end) -
sum(case when group_code not in ('total','others') then graduates end)
from yourtable
