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

练习记录，目的是复习SQL编写，使自己的SQL编写能力得到进一步的提升。

练习一：

select t.deptno,count(*) as CNT from emp t group by t.deptno ORDER BY 1

转换成：

select SUM(case when t.deptno = 10 then 1 ELSE 0 end) as DEPTNO_10,
       SUM(case when t.deptno = 20 then 1 ELSE 0 end) as DEPTNO_20,
       SUM(case when t.deptno = 30 then 1 ELSE 0 end) as DEPTNO_30
  from emp t;

练习二：select t.job,t.ename from emp t order by 1

转换成：

select RN,max(case when t.job='CLERK' then t.ename else null end) as CLERK,
max(case when t.job='ANALYST' then t.ename else null end) as ANALYSTS,
max(case when t.job='MANAGER' then t.ename else null end) as MGRS,
max(case when t.job='PRESIDENT' then t.ename else null end) as PREZ,
max(case when t.job='SALESMAN' then t.ename else null end) AS SALES  from
(SELECT T.JOB,T.ENAME,ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY ENAME) RN FROM EMP T) t
group by rn order by rn

练习三：

select SUM(case when t.deptno = 10 then 1 ELSE 0 end) as DEPTNO_10,
       SUM(case when t.deptno = 20 then 1 ELSE 0 end) as DEPTNO_20,
       SUM(case when t.deptno = 30 then 1 ELSE 0 end) as DEPTNO_30
  from emp t

转换成：反向转置结果集，运用笛卡儿积

select b.deptno,
case b.deptno
when  10 then a.DEPTNO_10
when  20 then a.DEPTNO_20
when  30 then a.DEPTNO_30 end as counts_by_dept  from
(select SUM(case when t.deptno = 10 then 1 ELSE 0 end) as DEPTNO_10,
       SUM(case when t.deptno = 20 then 1 ELSE 0 end) as DEPTNO_20,
       SUM(case when t.deptno = 30 then 1 ELSE 0 end) as DEPTNO_30
  from emp t)a,(select distinct deptno from scott.dept where deptno in(10,20,30))b

、

练习四：将结果集反向转置为一列：

写法一：select case rn
            when 1 then ename
            when 2 then job
            when 3 then cast(sal as char(4)) end as emps
from (
select row_number() over(partition by t.empno order by t.empno) as rn,t.ename,t.job,t.sal  from emp t,
(select * from emp where job='CLERK') four_rows
where t.deptno = 10) x

写法二：

select case id when 1 then ename
               when 2 then job
               when 3 then to_char(sal) else null end as emps from(
select t10.id,t.* from
(select row_number() over(order by ename) as no,t.ename,t.job,t.sal  from emp t where  deptno =10)t ,t10
where t10.id <=4)
order by no,id;