TSQL CASE与SELECT语句中的if比较 [英] TSQL CASE with if comparison in SELECT statement
问题描述
我想在SELECT中使用CASE语句.
I would like to use CASE statement in SELECT.
我从用户表中进行选择,并且(作为一个属性)我也使用了嵌套SQL:
I select from user table, and (as one attribute) I also use nested SQL:
SELECT
registrationDate,
(SELECT COUNT(*) FROM Articles WHERE userId = Users.userId) as articleNumber,
hobbies, ...
FROM USERS
然后我想执行一个CASE语句来获取用户的排名(排名取决于articleNumber).
and then I would like to do a CASE statement to get rank of user (rank is dependent on articleNumber).
我尝试过这样:
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:
消息207,级别16,状态1,过程GetUserList,行XY
无效的列名"articleNumber".
Msg 207, Level 16, State 1, Procedure GetUserList, Line XY
Invalid column name 'articleNumber'.
我想,CASE无法识别"我的嵌套SELECT.
CASE doesn't "recognize" my nested SELECT, I guess.
我帮助自己解决了其他一些问题,例如 SQL Server 2008-SELECT子句中的Case/If语句 但似乎没有任何作用.
I helped myself with some other solutions like SQL Server 2008 - Case / If statements in SELECT Clause but nothing seems to work.
我也没有发现'<'有任何类似的问题和>"比较.
I also didn't find any similar problem with '<' and '>' comparison.
任何帮助将不胜感激;)
Any help would be greatly appreciated ;)
推荐答案
请在外部选择相同的内容.您无法在同一查询中访问别名.
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
这篇关于TSQL CASE与SELECT语句中的if比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!