Почему я не могу ссылаться на псевдоним столбца в ORDER BY, используя CASE?

Извините, если это дубликат, но я его не нашел. Почему я не могу использовать свой псевдоним столбца, определенный в SELECT, из ORDER BY, когда я использую CASE?

Рассмотрим этот простой запрос:

SELECT NewValue=CASE WHEN Value IS NULL THEN '<Null-Value>' ELSE Value END
FROM dbo.TableA
ORDER BY CASE WHEN NewValue='<Null-Value>' THEN 1 ELSE 0 END

В результате возникает ошибка:

Недопустимое имя столбца 'NewValue'

Здесь sql-скрипт. (Замените ORDER BY NewValue на CASE WHEN..., который был закомментирован)

Я знаю, что в этом случае я могу использовать ORDER BY CASE WHEN Value IS NULL THEN 1 ELSE 0 END как здесь, но на самом деле запрос является более сложным и я хочу, чтобы он был максимально читабельным. Должен ли я использовать подзапрос или CTE, если да, почему это так?

Обновить, так как Микаэль Эрикссон прокомментировал любое выражение в сочетании с псевдонимом не допускается. Поэтому даже этот (бессмысленный запрос) выходит по той же причине:

SELECT '' As Empty
FROM dbo.TableA
ORDER BY Empty + ''

Результат:

Недопустимое имя столбца "Пусто".

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

Ответы

Ответ 1

Это связано с тем, как SQL dbms разрешает неоднозначные имена.

Я еще не отслеживал это поведение в стандартах SQL, но, похоже, он согласован между платформами. Вот что происходит.

create table test (
  col_1 integer,
  col_2 integer
);

insert into test (col_1, col_2) values 
(1, 3), 
(2, 2), 
(3, 1);

Псевдоним "col_1" как "col_2" и используйте псевдоним в предложении ORDER BY. Dbms разрешает "col_2" в ORDER BY в качестве псевдонима для "col_1" и сортирует значения в "test". "Col_1".

select col_1 as col_2
from test
order by col_2;

col_2
--
1
2
3

Опять же, псевдоним "col_1" как "col_2", но используйте выражение в предложении ORDER BY. Dbms разрешает "col_2" не как псевдоним для "col_1", а как столбец "test". "Col_2". Он сортируется по значениям в "тесте". "Col_2".

select col_1 as col_2
from test
order by (col_2 || '');

col_2
--
3
2
1

Итак, в вашем случае ваш запрос терпит неудачу, потому что dbms хочет разрешить "NewValue" в выражении в виде имени столбца в базовой таблице. Но это не так; это псевдоним столбца.

PostgreSQL

Это поведение описано в PostgreSQL в разделе Сортировка строк. Их обоснованное обоснование заключается в уменьшении двусмысленности.

Обратите внимание, что имя выходного столбца должно быть автономным, то есть оно не может использоваться в выражении - например, это не правильно:

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;          -- wrong

Это ограничение сделано для уменьшения двусмысленности. Существует еще двусмысленность, если элемент ORDER BY является простым именем, которое может соответствовать имени выходного столбца или столбцу из выражения таблицы. В таких случаях используется выходной столбец. Это может привести к путанице, если вы используете AS для переименования выходного столбца для сопоставления имени другого столбца таблицы.

Ошибка документации в SQL Server 2008

Несколько другая проблема в отношении псевдонимов в предложении ORDER BY.

Если имена столбцов псевдонимы в списке SELECT, в предложении ORDER BY может использоваться только псевдоним.

Если я недостаточно кофеен, это не так. Этот оператор сортируется по "тесту". "Col_1" как в SQL Server 2008, так и в SQL Server 2012.

select col_1 as col_2
from test
order by col_1;

Ответ 2

Вы можете попробовать что-то вроде:

select NewValue from (
  SELECT (CASE WHEN Value IS NULL THEN '<Null-Value>' ELSE Value END ) as NewValue, 
  ( CASE WHEN NewValue='<Null-Value>' THEN 1 ELSE 0 END) as ValOrder
  FROM dbo.TableA
  GROUP BY Value
) t
ORDER BY ValOrder