SQL Case when 的使用方法

统计每个职业 男女 每个等级 各有多少

    SELECT  CASE roleType WHEN 1 THEN '战士'

                      WHEN 2 THEN '法师'

                      WHEN 3 THEN '潜伏者'

                      WHEN 4 THEN '大祭司'

                      WHEN 5 THEN '弓箭手'

                      END AS role,

         CASE sex WHEN 0 THEN '女'

                  WHEN 1 THEN '男'

                  END AS sexb,LEVEL,

          COUNT(1) count1

 FROM t_player

 GROUP BY role,sexb,LEVEL

 ORDER BY role,sexb,LEVEL

 

--简单Case函数

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

--Case搜索函数

CASE WHEN sex = '1' THEN '男'

WHEN sex = '2' THEN '女'

ELSE '其他' END

 

也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下； 

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;