目录
2、case when(+聚合函数+group by实现行转列)
3、分段显示 case when 字段 between ...
case when 语法:
case when .. then .. else ..end
注: else 可省略,默认为空。
case when 常用几种用法:
1、case when 条件判断,用于字段
(1)case 字段 when 条件 then 结果1 else 结果2 end
select case deptno when '10' then avg1 else avg2 end from emp;
(2)case when 字段+条件 then 结果1 else 结果2 end
select case when deptno = '10' then avg1 else avg2 end from emp;
2、case when(+聚合函数+group by实现行转列)
select sum(case when deptno=10 then avg1 end) as bm10
,sum(case when deptno=20 then avg1 end) as bm20
,sum(case when deptno=30 then avg1 end) as bm30
,sum(case when deptno is null then avg1 end) as bm00
from (select deptno
,avg(sal) as avg1
from emp
group by deptno
);
3、分段显示 case when 字段 between ...
--1.对成绩表中的成绩进行分段,显示[100-85],[85-70],[70-60],[ <60]
select a.*
,case when round(score,2) between 85 and 100 then '[100-85]'
when round(score,2) between 70 and 84.99 then '[85-70]'
when round(score,2) between 60 and 69.99 then '[70-60]'
else '[<60]'
end as '分类'
from sc_a01 a
;