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

SQL Server 2005 error when grouping using subquery(使用子查询进行分组时出现 SQL Server 2005 错误)

问题描述

使用 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 的任何引用都将被视为内部引用.

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'