基本用法:case -- when -- then -- when --then --else
select case when ename in ('SMITH','WARD') then 1
when ename = 'ALLEN' then 2 else 3 end AAAA,
case when mgr IN( 9000,7782) then 4
when mgr = 8000 then 5 else 6 end BBBB
from emp
===================================================
sum -- case when -- then -- when -- then --else 配合:group by
select SAL,SUM(case when ename in ('SMITH','WARD') then 1
when ename = 'ALLEN' then 2 else 3 end) AAAA,
SUM(case when mgr IN( 9000,7782) then 4
when mgr = 8000 then 5 else 6 end) BBBB
from emp
GROUP BY SAL;
====================================================
group by 后面跟 case when
SELECT
CASE WHEN sal <= 500 THEN '1'WHEN sal > 500 AND sal <= 1300 THEN '2'
WHEN sal > 1300 AND sal <= 2200 THEN '3'
WHEN sal > 2200 AND sal <= 6000 THEN '4'
ELSE NULL END sal_class,
COUNT(*)
FROM emp
GROUP BY
CASE WHEN sal <= 500 THEN '1'
WHEN sal > 500 AND sal <= 1300 THEN '2'
WHEN sal > 1300 AND sal <= 2200 THEN '3'
WHEN sal > 2200 AND sal <= 6000 THEN '4'
ELSE NULL END;
==============================================
select ename,sal,case job when 'CLERK' then sal+100
when 'SALESMAN' then sal+200
when 'MANAGER' then sal+300
when 'ANALYST' then sal+400
when 'PRESIDENT' then sal+500
end 涨后工资 from emp;