Ошибка SQL Server 2005 при группировке с использованием подзапроса

Используя SQL Server 2005, я пытаюсь сгруппировать на основе оператора case с подзапросом, но получаю сообщение об ошибке («Каждое выражение GROUP BY должно содержать хотя бы одну ссылку на столбец»). Я могу легко обойти это, но может ли кто-нибудь объяснить ошибку? У меня есть ссылка на столбец #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

[править] Примечание (в ответ на комментарий) коррелированные и некоррелированные подзапросы:

--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

sql-server group-by subquery sql-server-2005
person Simon D    schedule 17.06.2009    source источник
comment
Группировка по 1 и 0 не является проблемой: выберите случай, когда заголовок = 1, затем 1, иначе 0 end headeris1, count() from #header group by case, когда заголовок = 1, затем 1, иначе 0 end Это существование вызывает это, но я не понимаю, почему это должно быть. (Глупая) переписывание ниже ошибок: подзапрос нельзя использовать в группе по выражению. выберите случай, когда 1 = (выберите верхний 1 заголовок из #header b, где b.header = a.header), затем 1, иначе 0 end headeris1, count() из #header группа по регистру, когда 1 = (выберите верхний 1 заголовок из #header b где b.header = a.header) затем 1 else 0 end Почему я не могу использовать подзапрос в группе by?   -  person Simon D    schedule 17.06.2009
comment
Настоящий вопрос заключается в том, с какой стати вы вообще используете коррелированные подзапросы? Они могут быть убийцами производительности, и соединения обычно работают лучше, так почему бы не начать с использования соединений и использовать только коррелированный подзапрос, если вы не можете заставить соединение работать (я никогда не видел такого случая, ваш пробег может отличаться). Как правило, когда есть несколько способов сделать что-то, никогда не следует начинать с того, который работает хуже всего.   -  person HLGEM    schedule 18.06.2009
comment
Я использовал коррелированные подзапросы, потому что это, казалось, логически отражало то, что я хочу сделать лучше всего - для каждого заголовка посмотреть, существуют ли какие-либо записи, и не беспокоиться о том, сколько их. Я не понимаю, почему коррелированные подзапросы обязательно будут работать хуже. В качестве теста я добавил пару запросов внизу моего вопроса. Глядя на планы выполнения, план с соединением имеет стоимость запроса 73% по сравнению с коррелированным подзапросом со стоимостью запроса 27%. В файле exists сохраняется сканирование таблицы на #detail. Я могу ошибаться, но похоже, что в этом примере коррелированный подзапрос работает лучше.   -  person Simon D    schedule 18.06.2009

Ответы (2)


arrow_upward
3
arrow_downward

Для начала, если мы укажем полную ошибку, она должна выглядеть так: «Каждое выражение GROUP BY должно содержать хотя бы один столбец, который не является внешней ссылкой».

Чтобы понять ошибку, нам нужно уточнить, что подразумевается под 'внешней ссылкой'.

(Примечание: в данном случае это не имеет ничего общего с внутренними или внешними соединениями)

внутренний и внешний относятся к основному запросу и его подзапросам. В этом случае EXISTS является подзапросом, и это коррелированный подзапрос, поскольку он имеет внешнюю ссылку #header.header, которая ссылается на внешнюю таблицу #header, тогда как любая ссылка на #detail будут рассматриваться как внутренние ссылки.

Таким образом, по сути, поскольку CASE использует коррелированный подзапрос, который ссылается на внешний запрос, это вызывает состояние ошибки, потому что это сообщение об ошибке появляется, когда вы пытаетесь использовать только выражения в предложении GROUP BY, которые интерпретируются как внешние ссылки.

Подзапросы могут использоваться в GROUP BY, но не коррелированные подзапросы.

Как ни странно, та же самая ошибка может быть сгенерирована более простым запросом без подзапроса, таким как

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

Прозрачный как грязь?

Кев

person Kev Riley    schedule 17.06.2009
comment
Спасибо за подробный ответ! Теперь мне довольно ясно, почему мы получаем ошибку. Чего я до сих пор не понимаю, так это почему вы не можете использовать коррелированные подзапросы в группе по выражению. - person Simon D; 18.06.2009

arrow_upward
0
arrow_downward

Вы говорите ему группировать по 1 или 0, когда вам нужно указать фактический столбец для группировки по (заголовку), а не по значению.

Итак, если я правильно понимаю, вам нужен список заголовков и количество их подробных записей?

Это может сработать для вас?

SELECT DISTINCT h.header, COUNT(d.detail) AS detail_count
FROM #header AS h
LEFT JOIN #detail AS d ON d.header = h.header
GROUP BY h.header, d.detail

С такими результатами, как...

header   detail_count
1       1
2       1
3       0
person MDStephens    schedule 17.06.2009
comment
Да, это сработает, чтобы получить результаты, которые мне нужны, и, вероятно, это более аккуратный способ сделать это, чем мой. Но этот вопрос заключается именно в том, почему я получаю сообщение об ошибке, а не в том, как переписать запрос. - person Simon D; 17.06.2009