题目九:返回未包含在GROUP BY 中的列
找到每个部门中工资最高和最低的员工,以及每种职位中工资最高和最低的员工,希望返回的结果集如下:
实现SQL:
SELECT deptno,ename,job,sal,max_dep_sal,min_dep_sal,
case sal when min_dep_sal then 'LOW SAL IN DEPT'
when max_dep_sal then 'TOP SAL IN DEPT' ELSE NULL END AS DEPT_STATUS,
case sal when min_JOB_sal then 'LOW SAL IN JOB'
when max_JOB_sal then 'TOP SAL IN JOB' ELSE NULL END AS JOB_STATUS FROM (
select t.deptno,t.ename,t.job,t.sal,
max(sal)over(partition by t.deptno order by t.deptno) as max_dep_sal,
min(sal)over(partition by t.deptno order by t.deptno) as min_dep_sal,
max(sal)over(partition by t.JOB order by t.JOB) as max_JOB_sal,
min(sal)over(partition by t.JOB order by t.JOB) as min_JOB_sal
from emp t) ORDER BY DEPTNO,SAL
case sal when min_dep_sal then 'LOW SAL IN DEPT'
when max_dep_sal then 'TOP SAL IN DEPT' ELSE NULL END AS DEPT_STATUS,
case sal when min_JOB_sal then 'LOW SAL IN JOB'
when max_JOB_sal then 'TOP SAL IN JOB' ELSE NULL END AS JOB_STATUS FROM (
select t.deptno,t.ename,t.job,t.sal,
max(sal)over(partition by t.deptno order by t.deptno) as max_dep_sal,
min(sal)over(partition by t.deptno order by t.deptno) as min_dep_sal,
max(sal)over(partition by t.JOB order by t.JOB) as max_JOB_sal,
min(sal)over(partition by t.JOB order by t.JOB) as min_JOB_sal
from emp t) ORDER BY DEPTNO,SAL
题目十:计算简单的小计,统计表EMP中各JOB的工资总和和放入一个结果集
实现SQL:
select case grouping(job) when 0 then job else 'TOTAL' end job,sum(sal) sal from emp t
group by rollup(job)
group by rollup(job)