SQL TSQL CASE 与 SELECT 语句中的 if 比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14231353/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL CASE with if comparison in SELECT statement
提问by CyberHawk
I would like to use CASE statement in SELECT.
我想在 SELECT 中使用 CASE 语句。
I select from user table, and (as one attribute) I also use nested SQL:
我从用户表中选择，并且（作为一个属性）我还使用嵌套 SQL：
SELECT
registrationDate,
(SELECT COUNT(*) FROM Articles WHERE userId = Users.userId) as articleNumber,
hobbies, ...
FROM USERS
and then I would like to do a CASE statement to get rank of user (rank is dependent on articleNumber).
然后我想做一个 CASE 语句来获得用户的等级（等级取决于文章编号）。
I tried like this:
我试过这样：
SELECT
registrationDate,
(SELECT COUNT(*) FROM Articles WHERE Articles.userId = Users.userId) as articleNumber,
ranking =
CASE
WHEN articleNumber < 2 THEN 'Ama'
WHEN articleNumber < 5 THEN 'SemiAma'
WHEN articleNumber < 7 THEN 'Good'
WHEN articleNumber < 9 THEN 'Better'
WHEN articleNumber < 12 THEN 'Best'
ELSE 'Outstanding'
END,
hobbies, etc...
FROM USERS
Parsing displays no error, but when I try to run it I get error:
解析没有显示错误，但是当我尝试运行它时出现错误：
Msg 207, Level 16, State 1, Procedure GetUserList, Line XY
Invalid column name 'articleNumber'.
消息 207，级别 16，状态 1，过程 GetUserList，行 XY
列名称“articleNumber”无效。
CASE doesn't "recognize" my nested SELECT, I guess.
我猜 CASE 不会“识别”我的嵌套 SELECT。
I helped myself with some other solutions like SQL Server 2008 - Case / If statements in SELECT Clausebut nothing seems to work.
我帮助自己解决了其他一些解决方案，例如 SQL Server 2008 - SELECT 子句中的 Case / If 语句，但似乎没有任何效果。
I also didn't find any similar problem with '<' and '>' comparison.
我也没有发现 '<' 和 '>' 比较有任何类似的问题。
Any help would be greatly appreciated ;)
任何帮助将不胜感激 ;）
回答by TechDo
Please select the same in the outer select. You can't access the alias name in the same query.
请在外部选择中选择相同的。您无法在同一查询中访问别名。
SELECT *, (CASE
WHEN articleNumber < 2 THEN 'Ama'
WHEN articleNumber < 5 THEN 'SemiAma'
WHEN articleNumber < 7 THEN 'Good'
WHEN articleNumber < 9 THEN 'Better'
WHEN articleNumber < 12 THEN 'Best'
ELSE 'Outstanding'
END) AS ranking
FROM(
SELECT registrationDate, (SELECT COUNT(*) FROM Articles WHERE Articles.userId = Users.userId) as articleNumber,
hobbies, etc...
FROM USERS
)x
回答by Justin
Should be:
应该：
SELECT registrationDate,
(SELECT CASE
WHEN COUNT(*)< 2 THEN 'Ama'
WHEN COUNT(*)< 5 THEN 'SemiAma'
WHEN COUNT(*)< 7 THEN 'Good'
WHEN COUNT(*)< 9 THEN 'Better'
WHEN COUNT(*)< 12 THEN 'Best'
ELSE 'Outstanding'
END as a FROM Articles
WHERE Articles.userId = Users.userId) as ranking,
(SELECT COUNT(*)
FROM Articles
WHERE userId = Users.userId) as articleNumber,
hobbies, etc...
FROM USERS
回答by Dia Agha
You can try with this:
你可以试试这个：
WITH CTE_A As (SELECT COUNT(*) as articleNumber,A.UserID as UserID FROM Articles A
Inner Join Users U
on A.userId = U.userId
Group By A.userId , U.userId ),
B as (Select us.registrationDate,
CASE
WHEN CTE_A.articleNumber < 2 THEN 'Ama'
WHEN CTE_A.articleNumber < 5 THEN 'SemiAma'
WHEN CTE_A.articleNumber < 7 THEN 'Good'
WHEN CTE_A.articleNumber < 9 THEN 'Better'
WHEN CTE_A.articleNumber < 12 THEN 'Best'
ELSE 'Outstanding'
END as Ranking,
us.hobbies, etc...
FROM USERS Us Inner Join CTE_A
on CTE_A.UserID=us.UserID)
Select * from B
