可以将case语句应用于select语句中生成的列上吗？

Question

我在select语句中生成了一列，现在我想对它应用一个case语句。我知道case语句只能应用于数据库中存在的列，但我想知道是否有其他选择？

我的代码是：

SELECT B.reg_no,B.dist_no,B.RDT_NAME,A.YTD_PQ, 
RANK() OVER (PARTITION BY B.reg_no ORDER BY A.YTD_PQ DESC) AS Rank_1, 
CASE Rank_1 WHEN '1' THEN 'YES' ELSE 'NO' END AS NVARCHAR(10) 
FROM OTHER_AWARDS AS B 
JOIN MT_D AS A 
ON A.RDT = B.RDT 

现在我想知道如何申请案在其上的select语句生成的列RANK_1。

Answer 1

我记得你不能引用计算列在同一级别的语句上，因为它在你引用它时不知道（或处理），但重用在case statement should work，so try this：

SELECT B.reg_no,B.dist_no,B.RDT_NAME,A.YTD_PQ, 
RANK() OVER (PARTITION BY B.reg_no ORDER BY A.YTD_PQ DESC) AS Rank_1, 
CASE RANK() OVER (PARTITION BY B.reg_no ORDER BY A.YTD_PQ DESC) WHEN '1' THEN 'YES' ELSE 'NO' END 
FROM OTHER_AWARDS AS B 
JOIN MT_D AS A 
ON A.RDT = B.RDT 

Answer 2

是的。包裹查询作为内查询和所有列然后可以像普通列处理，例如：

select case when computed_column ... end 
from (
    select rank() ... as computed_column 
    from ... 
} q 

而你的情况看起来像：

SELECT *, CASE Rank_1 WHEN '1' THEN 'YES' ELSE 'NO' END AS NVARCHAR(10)  
FROM (
    SELECT B.reg_no,B.dist_no,B.RDT_NAME,A.YTD_PQ, 
    RANK() OVER (PARTITION BY B.reg_no ORDER BY A.YTD_PQ DESC) AS Rank_1 
    FROM OTHER_AWARDS AS B 
    JOIN MT_D AS A ON A.RDT = B.RDT 
) q