TSQL CASE与如果SELECT语句比较(TSQL CASE with if compariso

我想用CASE语句中SELECT。

我从用户表中选择，以及（作为一个属性）我也使用嵌套SQL：

SELECT 
   registrationDate, 
   (SELECT COUNT(*) FROM Articles WHERE userId = Users.userId) as articleNumber, 
   hobbies, ...
FROM USERS

然后我希望做一个CASE语句获取的用户级别（级别取决于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

解析显示没有错误，但是当我尝试运行它，我得到的错误：

消息207，级别16，状态1，过程GetUserList，行XY
无效的列名称articleNumber“。

CASE不“承认”我的嵌套的SELECT，我猜。

我顺手拿与像一些其他的解决方案SQL Server 2008中-案例/如果在SELECT子句中的语句 ，但似乎没有任何工作。

我也没有找到与“<”和“>”相比任何类似的问题。

任何帮助将不胜感激 ;）

Answer 1:

请选择外部选择一样。 你不能在同一个查询访问的别名。

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

Answer 2:

应该：

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

Answer 3:

你可以用这个试试：

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

文章来源: TSQL CASE with if comparison in SELECT statement