SQL中的case when then else end用法

case end干啥的

类似于python中的if else判断, CASE一个字段, when这个字段的值是 '1'的时候返回'男', '2'返回'女'. END结束 as一个标题sex

SELECT
    s.s_id,
    s.s_name,
    s.s_sex,
CASE s.s_sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他'
END as sex,
    s.s_age,
    s.class_id
FROM
    student s

也可以这样写,CASE后不加字段,在 WHEN的时候指定字段名!

SELECT
    s.s_id,
    s.s_name,
    s.s_sex,
CASE
WHEN s.s_sex = '1' THEN '男'
WHEN s.s_sex = '2' THEN '女'
ELSE '其他'
END as sex,
 s.s_age,
 s.class_id
FROM
    t_b_student s

举个分组的示例

统计各个国家的男女人数

SELECT
    country,
    sum(case when p.sex=1 then p.population else 0 end) as '男',
    sum(case when p.sex=2 then p.population else 0 end) as '女'
FROM people as p 
GROUP BY country

统计国家人数表中属于各个洲的的总人数

创建表并插入数据

CREATE TABLE `people` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(55) DEFAULT NULL,
  `population` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试';

insert into people (country, population) 
VALUES
('中国', 210),
('日本', 10),
('印度', 312312),
('美国', 23);

查询

SELECT 
    case p.country 
    when '中国' then '亚洲'
    when '日本' then '亚洲'
    when '印度' then '亚洲'
    else '北美洲' 
    end as '洲',
    sum(p.population) as '人口'
FROM people as p
GROUP BY case p.country
            when '中国' then '亚洲'
            when '日本' then '亚洲'
            when '印度' then '亚洲'
            else '北美洲' 
            end

结果