2022 4 14 xiawu

 with tt as (select sal, case
when sal<2000 then '贫困'
when sal>4000 then '资本家'
else '中产'
end elevel from emp order by sal) 
select elevel,count(1) from tt group by elevel;  
  select count(1) into c from emp e where e.deptno=dno;
  if c !=0 then
  for row in (select * from emp e where dno=e.deptno) loop
    dbms_output.put_line('员工工资'||row.sal ||'员工的奖金'||
    nvl(row.comm,0) ||'员工的总收入'||nvl(row.comm+row.sal,0+row.sal));
  end loop;
  else
    dbms_output.put_line('错误的部门编号');
  end if;  
end;

select area, name, sum(quantity) from aat
group by rollup(area,name);

select area, name, color, sum(quantity) from aat
group by grouping sets(area,name,color);

--对emp表，使用rollup函数，对字段deptno和job分组统计，解释结果。
select e.deptno, e.job from emp e group by rollup(e.deptno,e.job);
--对emp表，使用cube函数，对字段deptno、job分组统计，解释结果
select e.deptno, e.job from emp e group by cube(e.deptno,e.job);

select e.*, sum(sal) over() 
from emp e

select e.*,sum(e.sal) over(partition by e.deptno)
from emp e;

select e.*, rank() over(order by sal desc) as 排名
from emp e;

select e.*, sum(sal) over(order by sal) as accumulute_sal  
from emp e;

select e.*, rank() 
over(partition by e.deptno order by sal desc) as 排名
from emp e;

select e.*,sum(sal)
over(partition by e.deptno order by sal desc) as sum_sal
 from emp e;

select t.rn,
sum(rn) over(order by rn rows 1 preceding) as t1,
sum(rn) over(order by rn rows between 1 preceding and current row) as t2,
sum(rn) over(order by rn rows between unbounded preceding and current row)as t3,
sum(rn) over(order by rn rows between current row and unbounded following) as t4,
sum(rn) over(order by rn rows between 1 preceding and 2 following) as t5,
sum(rn) over(order by rn rows between unbounded preceding and unbounded following)as t6,
sum(rn) over(order by rn rows between current row and current row) as t7 
from(select rownum as rn from dual connect by rownum<=10) t; 

select e.*,
count(e.hiredate) over(order by e.hiredate range between 100 preceding 
and 100 following)-1 人数 
from emp e;