用法1:
SELECT
CASE WHEN STATE = '1' THEN '成功'
WHEN STATE = '2' THEN '失败'
ELSE '其他' END
FROM SYS_SCHEDULER
用法2:
SELECT STATE
CASE WHEN '1' THEN '成功'
WHEN '2' THEN '失败'
ELSE '其他' END
FROM SYS_SCHEDULER
举例:
有员工表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;