一、MySQL case when的三种用法:


1.case 字段 when, 字段的具体值;

select a.*,
case sex
when '1' then '男'
else '女'
end as '性别'
FROM table_sex a;

2.case when 字段,可对字段进行取值范围设置;

SELECT a.*,
CASE
WHEN a.age BETWEEN 0 and 20 THEN '青年'
WHEN a.age BETWEEN 20 and 40 THEN '中年'
ELSE '非人类'
END AS '描述'
FROM table_c a;

3.case when 字段1,字段2,可对多个字段进行取值映射;

SELECT a.*,
CASE
WHEN a.age BETWEEN 20 and 80 THEN '青年'
WHEN a.name ='流浪' THEN '帅气'
END AS '描述'
FROM table_cc a

注:如果两个条件都针对一个字段的话,会显示第一个when的值。


二、具体解释:

SELECT
case -------------如果
  when sex='' then '男' -------------sex='1',则返回值'男'
  when sex='' then '女' -------------sex='2',则返回值'女'
   else 0 -------------其他的返回'其他’
end -------------结束
from sys_user --------整体理解: 在sys_user表中如果sex='1',则返回值'男'如果sex='2',则返回值'女' 否则返回'其他’

三、例子

有员工表如下:

empinfo (
  Fempno varchar2(10) not null pk,
  Fempname varchar2(20) not null,
  Fage number not null,
  Fsalary number not null
);

假如数据量很大约1000万条;

写一个你认为最高效的SQL,用一个SQL计算以下四种人:

  • fsalary>9999 and fage > 35
  • fsalary>9999 and fage < 35
  • fsalary <9999 and fage > 35
  • fsalary <9999 and fage < 35

每种员工的数量

SELECT
SUM(CASE WHEN fsalary > 9999 AND fage > 35 THEN 1 ELSE 0 END)
AS "fsalary>9999_fage>35",
SUM(CASE WHEN fsalary > 9999 AND fage < 35 THEN 1 ELSE 0 END)
AS "fsalary>9999_fage<35",
SUM(CASE WHEN fsalary < 9999 AND fage > 35 THEN 1 ELSE 0 END)
AS "fsalary<9999_fage>35",
SUM(CASE WHEN fsalary < 9999 AND fage < 35 THEN 1 ELSE 0 END)
AS "fsalary<9999_fage<35"
FROM empinfo;

END 2018-05-16 14:30:48

05-11 22:27