SQL Server 无法对包含聚合或子查询的表达式执行聚合函数 [英] SQL Server Cannot perform an aggregate function on an expression containing an aggregate or a subquery

问题描述

我的存储过程有问题.

我收到错误:

无法对包含表达式的表达式执行聚合函数聚合或子查询

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

这是我认为发生错误的存储过程的一部分:

Here's the part of my stored procedure where I believe the error occurs:

SELECT column_1, column_2,
       SUM(CASE WHEN column_2 NOT IN (SELECT product FROM table_products) THEN 1
                ELSE 0
           END) AS Total
FROM my_table 
WHERE is_rated = '1'
GROUP BY column_1, column_2 

谢谢.

推荐答案

如果您无论如何都尝试避免相关子查询，通常会获得更好的性能:

You'll get much better performance generally if you try to avoid correlated subqueries anyway:

SELECT
    MT.column_1,
    MT.column_2,
    SUM(CASE WHEN P.product IS NULL THEN 1 ELSE 0 END) AS total
FROM
    My_Table MT
LEFT OUTER JOIN Products P ON P.product = MT.column_2
WHERE
    MT.is_rated = '1'
GROUP BY
    MT.column_1,
    MT.column_2

这假设 Products 表中最多只有一个匹配项(Products，而不是 Table_Products - 当然它是一个表，所以不要把它放在名称中).换句话说，如果 product 是 Products 表的 PK(或 AK)，这将起作用.

This assumes that there will only ever be at most one match in the Products table (Products, not Table_Products - of course it's a table so don't put that in the name). In other words, this will work if product is the PK (or an AK) of the Products table.

如果情况并非如此，并且您可能在 Products 表中有多个匹配项，那么您可以 JOIN 到使用 DISTINCT 的子查询product 列.

If that's not the case and you might have multiple matches in the Products table then you can JOIN to a subquery that uses DISTINCT on the product column.

这篇关于SQL Server 无法对包含聚合或子查询的表达式执行聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！