TSQL CASE with if comparison in SELECT statement

问题:

I would like to use CASE statement in SELECT.

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

and then I would like to do a CASE statement to get rank of user (rank is dependent on articleNumber).

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'.

CASE doesn't "recognize" my nested SELECT, I guess.

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 ;)

回答1:

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

回答2:

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

回答3:

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