Почему я не могу GROUP BY 1, когда он ОК, чтобы ЗАКАЗАТЬ 1?

Почему порядковые номера столбцов являются законными для ORDER BY, но не для GROUP BY? То есть, может кто-нибудь сказать мне, почему этот запрос

SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY OrgUnitID

не может быть записано как

SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY 1

Когда законно писать запрос, например

SELECT OrgUnitID FROM Employee AS e ORDER BY 1

?

Мне действительно интересно, есть ли что-то тонкое относительно реляционного исчисления или что-то еще, что помешало бы группировке работать правильно.

Дело в том, что мой пример довольно тривиален. Общеизвестно, что столбец, который я хочу сгруппировать, на самом деле является вычислением, и повторение того же вычисления в GROUP BY является (а) раздражающим и (б) делает ошибки во время обслуживания намного более вероятными. Вот простой пример:

SELECT DATEPART(YEAR,LastSeenOn), COUNT(*)
    FROM Employee AS e
    GROUP BY DATEPART(YEAR,LastSeenOn)

Я бы подумал, что правило SQL нормализовать только представлять данные один раз в базе данных также должно распространяться и на код. Я бы хотел, чтобы это выражение вычислялось только один раз (в списке столбцов SELECT) и иметь возможность ссылаться на него по порядковому номеру в GROUP BY.

Уточнение: я специально работаю над SQL Server 2008, но, тем не менее, я задаюсь вопросом об общем ответе.

Ответы

Ответ 1

Одна из причин заключается в том, что ORDER BY - последнее, что работает в SQL Query, вот порядок операций

  • Предложение FROM
  • Предложение WHERE
  • Предложение GROUP BY
  • Предложение HAVING
  • Предложение SELECT
  • Предложение ORDER BY

поэтому, когда у вас есть столбцы из предложения SELECT, вы можете использовать порядковое позиционирование

EDIT, добавлено это на основе комментария Возьмите это, например,

create table test (a int, b int)
insert test values(1,2)
go

Следующий запрос будет анализироваться без проблем, он не будет работать

select a as b, b as a
     from test
    order by 6

вот ошибка

Msg 108, уровень 16, состояние 1, строка 3
Позиция ORDER BY 6 находится вне диапазона количества элементов в списке выбора.

Это также отлично разбирается

select a as b, b as a
     from test
    group by 1

Но он взорвется этой ошибкой

Msg 164, уровень 15, состояние 1, строка 3
Каждое выражение GROUP BY должно содержать по крайней мере один столбец, который не является внешней ссылкой.

Ответ 2

В SQL существует много элементарных несоответствий, и одним из них является использование скаляров. Например, любой может ожидать


    select * from countries
    order by 1

и


    select * from countries
    order by 1.00001

чтобы быть похожими запросами (различие между ними может быть сделано бесконечно малым, в конце концов), а это не так.

Ответ 3

использовать aliasses:

SELECT DATEPART(YEAR,LastSeenOn) as 'seen_year', COUNT(*) as 'count'
    FROM Employee AS e
    GROUP BY 'seen_year'

** EDIT **

если GROUP BY alias не разрешено для вас, здесь решение/обход:

SELECT seen_year
     , COUNT(*) AS Total 
  FROM (
    SELECT DATEPART(YEAR,LastSeenOn) as seen_year, *
    FROM Employee AS e
  ) AS inline_view
GROUP 
    BY seen_year

Ответ 4

Я не уверен, что стандарт указывает, действительно ли он действителен, но я считаю, что он зависит от реализации. Я просто попробовал свой первый пример с одним движком SQL, и он работал нормально.