高级函数-case

2024-10-01 11:52:22

case函数 (适合区间,>,<判断)

case when 判断表达式 then

when 判断表达式 then

.....

end

select deptno,count(*) total,

sum(case when sal>=2000 then 1

end) great,

sum(case when sal<2000 then 1

end) least

from emp

where deptno is not null

group by deptno;

//统计部门中男人数和女人数

create table j20(

id number(7),

name varchar(20),

sex char(1), --'M'或'F'

deptno number(7));

select deptno,

sum(decode(sex,'M',1,0)) as male,

sum(decode(sex,'F',1,0)) as female

from j20

group by deptno;

select deptno,

sum(case when sex='M' then 1

else 0 end) as male,

sum(case when sex='F' then 1

else 0 end) as female

from j20

group by deptno;

//基于EMP表查询部门编号,工资1000以内人数,

1000-2000人数,2000以上人数

select deptno,

sum(case when sal<1000 then 1

else 0

end) "1000以内",

sum(case when sal>=1000 and sal<2000 then 1

else 0

end) "1000-2000",

sum(case when sal>=2000 then 1

else 0

end) "2000以上"

from emp

group by deptno;

//查询调薪结果，规则：MANAGER涨10%,CLERK涨20%,

其他人涨5%

select empno,ename,job,sal,

decode(job,'MANAGER',sal*1.1,

'CLERK',sal*1.2,

sal*1.05) "加薪之后"

from emp;

最新文章

热门文章