Mysql case...when 与 case when

1. case....when

　　select 列名1, 列名2, case 列名3

　　　　when  条件1 then  值1
　　　　when  条件2 then  值2
　　　　when  条件3 then  值3
　　　　else  then  值4
　　　　end  as 别名
　　from 
       表名
   where
       过滤条件 ;

示例:

select name,case name
    when '赵光明' then '班长'
    when '钱仁义' then '语文课代表'
    when '吴胜利' then '数学课代表'
    when '陈二狗' then '体育课代表'
    when '程小鸭' then '英语课代表'
    when '郑国强' then '副班长'
    else '班级成员'
    end  as '班级职务'
from student_info ;

 如图:

 

2. case when

select 列名1,列名2, case

　　　　when 列名3= 条件值1 then 选项1

　　　　when 列名3=条件值2 then 选项2.......

　　　　else 默认值 

       end as 别名
from 
      别名
where 
      条件;

 

示例1.1:

    select name,course,score,
    case when score >=90 then '优'
        when score >=80 then '良'
        when score >=70 then '中'
        when score >=60 then '及格'
        else  '不及格'
        end as '考评'
    from score_info
    where course = '数学';

图例1.1:

示例1.2:

select
    name ,
    province,
    case
        when province in ('北京', '重庆', '上海', '南京') then '直辖市'
        when province in('内蒙古', '新疆', '宁夏', '西藏', '广西') then '自治区'
        when province in('香港','澳门') then '特别行政区'
        else '32省'
    end as category
from
    person_info order by category;

图例1.2:

 示例1.3

select
    name ,
    province,
    case
        when province in ('北京', '重庆', '上海', '南京') then '直辖市'
        when province in('内蒙古', '新疆', '宁夏', '西藏', '广西') then '自治区'
        when province in('香港', '澳门') then '特别行政区'
        else '32省'
    end as category,
    salary,
    case
        when salary >= 15000 then 'high'
        when salary >= 8000 then 'middle'
        else 'low'
    end as salary_level
from
    person_info
order by
    category;

图例1.3:

 

示例2.1:

select 
    sum(case when score >=90 then 1 else 0 end) as '优秀人数',
    sum(case when score >=80 and score<90 then 1 else 0 end) as '良好人数',
    sum(case when score >=70 and score<80 then 1 else 0 end) as '中等人数',
    sum(case when score >=60 then 1 else 0 end) as '及格人数',
    sum(case when score <60 then 1 else 0 end) as '不及格人数'
from  
    score_info  
where 
    course = '数学';

图例2.1

 

 

示例2.2

select course,
    sum(case when score >=90 then 1 else 0 end) as '优秀人数',
    sum(case when score >=80 and score<90 then 1 else 0 end) as '良好人数',
    sum(case when score >=70 and score<80 then 1 else 0 end) as '中等人数',
    sum(case when score >=60 then 1 else 0 end) as '及格人数',
    sum(case when score <60 then 1 else 0 end) as '不及格人数'
from  
    score_info  
group by 
    course;

图例2.2

 

 示例2.3 

 

SELECT  SUM(population), 
CASE country  WHEN '中国' THEN '亚洲' 
  WHEN '印度' THEN '亚洲' 
  WHEN '日本' THEN '亚洲' 
  WHEN '美国' THEN '北美洲' 
  WHEN '加拿大'  THEN '北美洲' 
  WHEN '墨西哥'  THEN '北美洲' 
ELSE '其他' END 
FROM    Table_A 
GROUP BY
CASE country 
WHEN '中国' THEN '亚洲' 
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲' 
WHEN '美国' THEN '北美洲' 
WHEN '加拿大'   THEN '北美洲' 
WHEN '墨西哥'   THEN '北美洲' 
ELSE '其他' END;