case when sqlserver

SELECT
        CASE WHEN salary <= 500 THEN '1'
             WHEN salary > 500 AND salary <= 600  THEN '2'
             WHEN salary > 600 AND salary <= 800  THEN '3'
             WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL END salary_class,
        COUNT(*)
FROM    Table_A

GROUP BY
        CASE WHEN salary <= 500 THEN '1'
             WHEN salary > 500 AND salary <= 600  THEN '2'
             WHEN salary > 600 AND salary <= 800  THEN '3'
             WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL END;

 

Case具有两种格式。简单Case函数和Case搜索函数。

--简单Case函数 CASE sex          WHEN '1' THEN '男'          WHEN '2' THEN '女' ELSE '其他' END

--Case搜索函数 CASE WHEN sex = '1' THEN '男'          WHEN sex = '2' THEN '女' ELSE '其他' END

例如：

SELECT id, name, cj, (CASE WHEN cj < 60 THEN '不及格' WHEN cj BETWEEN 60 AND 90 THEN '良好' WHEN cj > 90 THEN '优秀' END) AS 状态 FROM   stud