WHERE子句中列名无效，使用CASE选择的列

Question

我有一条比较复杂的SQL语句，从许多不同的表中选择数据，并为了处理糟糕的遗留数据结构，我有一些自定义列，它们的值基于其他列的值。我目前是通过 CASE 语句来解决这个问题：

 SELECT
     ...,
     CASE channel
         WHEN 1 THEN channel_1
         WHEN 2 THEN channel_2
         ...
         ELSE 0
     END AS ChannelValue,
     CASE channelu
         WHEN 1 THEN channelu_1
         WHEN 2 THEN channelu_2
         ...
         ELSE '0'
     END AS ChannelWithUnit,
     ...
 FROM 
     ... 
 --rest of statement continues with multiple joins and where/and clauses...

当我在MS SQL Server Management Studio中执行查询时，我得到了我期望的所有结果，并且列名按照我在AS子句中指定的方式列出。然而，由于某种原因，我不能在WHERE语句中使用条件值。如果我添加

AND ChannelValue > Limit * p.Percentage / 100

在查询结束时，我会在那行代码上收到一个错误提示：

Msg 207, Level 16, State 1, Line 152
Invalid column name 'ChannelValue'

为什么不允许这样做？我应该怎么做呢？

- Tomas Aschan

Answer 1

在SQL语句中，仅有在ORDER BY子句中声明SELECT列表别名才有效。对于查询的其他部分，您需要重复整个CASE表达式，并相信优化器能够识别它是相同的。

如果您使用的是SQL2005+，可以使用CTE来避免此问题，这有时会提高可读性。

WITH YourQuery As
(

 SELECT
     Limit, 
     Percentage,
     CASE channel
         WHEN 1 THEN channel_1
         WHEN 2 THEN channel_2
         ...
         ELSE 0
     END AS ChannelValue,
     CASE channelu
         WHEN 1 THEN channelu_1
         WHEN 2 THEN channelu_2
         ...
         ELSE '0'
     END AS ChannelWithUnit,
     ...
 FROM 
)

select ...
FROM YourQuery WHERE
ChannelValue > Limit * Percentage / 100

- Martin Smith

Answer 2

您不能在同一select级别中在where子句中使用ChannelValue列名。
您需要将整个select放入一个子查询中。

select ....
from 
( 
your select query
) as innerSelect
where ChannelValue > Limit * p.Percentage / 100

- Nikhil S

Answer 3

你可以使用CTE - 类似于以下内容：

WITH CTE AS
(
SELECT 
     ..., 
     CASE channel 
         WHEN 1 THEN channel_1 
         WHEN 2 THEN channel_2 
         ... 
         ELSE 0 
     END AS ChannelValue, 
     CASE channelu 
         WHEN 1 THEN channelu_1 
         WHEN 2 THEN channelu_2 
         ... 
         ELSE '0' 
     END AS ChannelWithUnit, 
     ... 
 FROM  
)
SELECT * 
FROM CTE
WHERE ChannelValue > Limit * p.Percentage / 100 

- StuartLC

Answer 4

-- SOMETHING FROM ADVENTURE WORKS THIS WORKS AS THE ABOVE POSTER
--- USING 'WITH CTE AS'
-- MY ANSWER TO A QUERY

WITH CTE AS
 (
 SELECT HE.Gender AS [GENDER], HE.HireDate AS [HIREDATE],      HE.BirthDate AS [BIRTHDATE],
CASE
WHEN DATEPART(YY,[BIRTHDATE]) BETWEEN 1962 AND 1970 AND [GENDER] = 'M' AND DATEPART(YY,[HIREDATE]) > 2001  THEN 'MALE'
WHEN DATEPART(YY,[BIRTHDATE]) BETWEEN 1972 AND 1975 AND [GENDER] = 'F' AND DATEPART(YY,[HIREDATE]) BETWEEN 2001 AND 2002 THEN 'FEMALE'
ELSE 'NOTREQUIRED'
END AS [RESULT]
FROM [HumanResources].[Employee] AS HE
)
SELECT *
FROM CTE
WHERE [RESULT] <> 'NOTREQUIRED' -- GOT THIS TOO WORK NO FEMALES IN RESULT
ORDER BY [RESULT]

- Redz Ch