报表与数据仓库运算-练习笔记2

题目九：返回未包含在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

题目十：计算简单的小计，统计表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)