使用子查询进行分组时出现 SQL Server 2005 错误

2022-01-23 00:00:00 group-by sql-server-2005 subquery sql-server

使用 SQL Server 2005，我尝试基于带有子查询的 case 语句进行分组，但出现错误(每个 GROUP BY 表达式必须包含至少一个列引用.").我可以很容易地解决它，但谁能解释这个错误?我有一个列引用#header.header.

Using SQL Server 2005 I'm trying to group based on a case statement with a subquery, but I'm getting an error ("Each GROUP BY expression must contain at least one column reference. "). I can work round it quite easily, but can anyone explain the error? I've got a column reference to #header.header.

create table #header (header int)
create table #detail (header int, detail int)

insert into #header values (1)
insert into #header values (2)
insert into #header values (3)

insert into #detail values (1, 1)
insert into #detail values (2, 1)

--error: Each GROUP BY expression must contain at least one column reference.
select case when exists (select 1 from #detail where #detail.header = #header.header) then 1 else 0 end hasrecords from #header
group by case when exists (select 1 from #detail where #detail.header = #header.header) then 1 else 0 end

--results I want
select hasrecords, count(*) from
(
    select case when exists (select 1 from #detail where #detail.header = #header.header) then 1 else 0 end hasrecords from #header
) hasrecords
group by hasrecords

drop table #header
drop table #detail

注意(响应评论)相关和非相关子查询:

[edit] Note (in response to comment) correlated and non-correlated subqueries:

--correlated
select header, case when exists (select 1 from #detail where #detail.header = #header.header) then 1 else 0 end hasrecords from #header

--non-correlated
select #header.header, case when count(#detail.header) > 0 then 1 else 0 end hasrecords from #header left join #detail on #header.header = #detail.header group by #header.header

推荐答案

首先，如果我们给出完整的错误，它应该是每个 GROUP BY 表达式必须至少包含一个不是外部引用的列."

To start, if we give the full error, it should read "Each GROUP BY expression must contain at least one column that is not an outer reference."

为了理解错误，我们需要澄清'外部引用'

To understand the error, we need to clarify what is meant by an 'outer reference'

(注意:在这种情况下，它与内部或外部连接无关)

(Note: in this case it has nothing to do with inner or outer joins)

inner 和 outer 参考主查询及其子查询.在这种情况下，EXISTS 是子查询，它是 correlated 子查询，因为它具有 #header.header<的外部引用/code>，它引用外部表 #header，而对 #detail 的任何引用都将被视为内部引用.

The inner and outer are in reference to the main query and it's subqueries. In this case the EXISTS is the subquery and it is a correlated subquery as it has an outer reference of #header.header, which is referencing the outer table #header, whereas any reference to #detail would be considered as inner references.

所以本质上，因为 CASE 使用引用外部查询的相关子查询，然后这会触发错误状态，因为当您尝试仅使用 GROUP BY 中的表达式时会出现此错误消息被解释为外部引用的子句.

So in essence, because the CASE utilises a correlated subquery that references the outer query, then this fires the error state, beacuse this error message appears when you try to use only expressions in a GROUP BY clause that are interpreted as outer references.

子查询可以用于 GROUP BY，但不能用于相关子查询.

Subqueries can be used in GROUP BY, but not correlated subqueries.

令人困惑的是，同样的错误也可能由非子查询、更简单的查询产生，例如

Confusingly, the same error can be generated by a non-subqueried, simpler query such as

select 
 case when header=1 then 1 
      else 0 
 end headeris1, 
 'constant' 
from #header 
group by case when header=1 then 1 else 0 end , 'constant'

甚至用 @variable

清如泥?

凯夫

相关文章