У меня есть таблица имен и адресов, которая включает столбец почтового индекса. Я хочу разделить пробелы от почтовых индексов и выбрать любой, который соответствует конкретному шаблону. Я пробую это (упростил немного) в T-SQL на SQL Server 2005:
SELECT Replace(Postcode, ' ', '') AS P
FROM Contacts
WHERE P LIKE 'NW101%'
Но я получаю следующую ошибку;
Msg 207, Level 16, State 1, Line 3
Invalid column name 'P'.
Если я удаляю оператор Where, я получаю список почтовых индексов без пробелов, который является тем, что я хочу искать. Как я должен приблизиться к этому? Что я делаю неправильно?
Не используйте псевдоним ( P
) напрямую в предложении WHERE
.
Вы можете снова использовать ту же логику REPLACE
в предложении WHERE
:
SELECT Replace(Postcode, ' ', '') AS P
FROM Contacts
WHERE Replace(Postcode, ' ', '') LIKE 'NW101%'
или использовать подзапрос с псевдонимом, как описано в ответах Ника.
Чтобы расширить ответ Одеда , ваша концептуальная модель здесь нуждается в небольшой корректировке. Псевдонимы имен столбцов (предложения AS
в списке SELECT
) происходят очень поздно при обработке SELECT
, поэтому псевдонимы недоступны для WHERE
пункты. Фактически, единственное, что происходит после псевдонима столбцов, - это сортировка, поэтому (цитируя документы на SELECT
):
алиас_столбца
можно использовать в предложении ORDER BY. Однако его нельзя использовать в предложенииWHERE
,GROUP BY
илиHAVING
.
Если у вас есть запутанное выражение в списке SELECT
, вы можете быть обеспокоены тем, что оно «оценивается дважды», когда оно появляется в списке SELECT
и (скажем) Предложение WHERE
- однако механизм запросов достаточно умен, чтобы понять, что происходит. Если вы не хотите, чтобы выражение появлялось дважды в вашем запросе, вы можете сделать что-то вроде
SELECT c1, c2, c3, expr1
FROM
( SELECT c1, c2, c3, some_complicated_expression AS expr1 ) inner
WHERE expr1 = condition
, что позволит избежать двойного физического появления some_complicated_expression
.
Вы можете ссылаться именно на этот способ, если заключите запрос в оболочку, например:
SELECT P
FROM (SELECT Replace(Postcode, ' ', '') AS P
FROM Contacts) innertable
WHERE P LIKE 'NW101%'
Обязательно укажите псевдоним для выбранного в оболочке выбора, даже неиспользуемый (SQL Server не позволяет это без одного IIRC)
, если вы хотите надеяться когда-либо использовать индекс, храните данные согласованным образом (с удаленными пробелами). Либо просто удалите пробелы, либо добавьте постоянный вычисляемый столбец. Затем вы можете просто выбрать из этого столбца и не добавлять все пространство, удаляя накладные расходы каждый раз, когда вы запускаете свой запрос.
добавить PERSISTED вычисляемый столбец:
ALTER TABLE Contacts ADD PostcodeSpaceFree AS Replace(Postcode, ' ', '') PERSISTED
go
CREATE NONCLUSTERED INDEX IX_Contacts_PostcodeSpaceFree
ON Contacts (PostcodeSpaceFree) --INCLUDE (covered columns here!!)
go
, чтобы просто исправить столбец, удалив использование пробелов:
UPDATE Contacts
SET Postcode=Replace(Postcode, ' ', '')
теперь вы можете искать так, либо select может использовать индекс:
--search the PERSISTED computed column
SELECT
PostcodeSpaceFree
FROM Contacts
WHERE PostcodeSpaceFree LIKE 'NW101%'
или
--search the fixed (spaces removed column)
SELECT
Postcode
FROM Contacts
WHERE PostcodeLIKE 'NW101%'
Вы создаете псевдоним P
и позже в , где
пункт вы используете то же самое, то есть что создает проблему. Не используйте P
в , где
, попробуйте вместо этого:
SELECT Replace(Postcode, ' ', '') AS P FROM Contacts
WHERE Postcode LIKE 'NW101%'
Вы должны повторять свое выражение везде, где хотите его использовать:
SELECT Replace(Postcode, ' ', '') AS P
FROM Contacts
WHERE Replace(Postcode, ' ', '') LIKE 'NW101%'
или вы можете сделать это подзапросом
select P
from (
SELECT Replace(Postcode, ' ', '') AS P
FROM Contacts
) t
WHERE P LIKE 'NW101%'
SELECT *
FROM Contacts
WHERE ContactId IN
(SELECT a.ContactID
FROM
(SELECT ContactId, Replace(Postcode, ' ', '') AS P
FROM Contacts
WHERE Postcode LIKE '%N%W%1%0%1%') a
WHERE a.P LIKE 'NW101%')