mySQL(14)-case when

两种：
 

第一种 格式 : 简单Case函数 :  
    case  字段         --此处有字段
    when  条件 then 结果 
    else  结果 
  end; 
第二种  格式 :Case搜索函数
    case               --没写字段
    when 条件 then 结果 
    when 条件 then 结果 
  else 结果 
  end;

示例1：
 

-- 1.建表
create table users(id int,name varchar(20),sex int);
-- 2.插入数据
insert into users(id,name) values(1,'张一');
insert into users(id,name,sex) values(2,'张二',1);
insert into users(id,name) values(3,'张三');
insert into users(id,name,sex) values(5,'张五',2);
insert into users(id,name,sex) values(6,'张六',1);
insert into users(id,name,sex) values(7,'张七',2);
insert into users(id,name,sex) values(8,'张八',1);

select u.id,u.name,u.sex,
       case u.sex
       when 1 then '男'
        when 2 then '女'
        else '空的'
        end   from users u;

结果：

示例2：
 

-- 1.创建表，插入数据
create table mycountry(id int,country varchar(20),population int);
insert into mycountry(country,population) values('中国',600);
insert into mycountry(country,population) values('美国',100);
insert into mycountry(country,population) values('加拿大',100);
insert into mycountry(country,population) values('英国',200);
insert into mycountry(country,population) values('法国',300);
insert into mycountry(country,population) values('日本',250);
insert into mycountry(country,population) values('德国',200);
insert into mycountry(country,population) values('墨西哥',50);
insert into mycountry(country,population) values('印度',250);

-- 查询
SELECT  SUM(population),
case country
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '日本'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END
FROM    mycountry      -- 从表中选择
GROUP BY CASE country  --根据前面的条件 case country 分组
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '日本'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END;

示例3：
 

-- 1.创建，插入数据
create table mysalary(id int,salary int);
insert into mysalary(id,salary) values(1,600);
insert into mysalary(id,salary) values(2,800);
insert into mysalary(id,salary) values(3,700);
insert into mysalary(id,salary) values(4,900);
insert into mysalary(id,salary) values(5,1000);
insert into mysalary(id,salary) values(6,800);
insert into mysalary(id,salary) values(7,700);
insert into mysalary(id,salary) values(8,600);
insert into mysalary(id,salary) values(9,500);
insert into mysalary(id,salary) values(10,400);
insert into mysalary(id,salary) values(11,800);
 
--2.查询
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            --end
salary_class,            -- salary_class COUNT(*) 为要显示的字段
COUNT(*)
FROM    mysalary
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;