7-60 レポート作成

SQLパズル

DeptEmpテーブル
DeptNO  EmpNO
------  -----
    10  1001
    10  1002
    10  1003
    20  2001
    20  2002
    20  2003
    20  2004
    20  2005
    30  3001
    30  3002
    30  3003
    30  3004
    30  3005
    30  3006

以下の出力をする。

出力結果
DeptNO  EmpNO  EmpCount  DeptCount
------  -----  --------  ---------
    10   1001         3          3
  null   1002      null       null
  null   1003      null       null
  null   null      null       null
    20   2001         5       null
  null   2002      null       null
  null   2003      null       null
  null   2004      null       null
  null   2005      null       null
  null   null      null       null
    30   3001         6       null
  null   3002      null       null
  null   3003      null       null
  null   3004      null       null
  null   3005      null       null
  null   3006      null       null

データ作成スクリプト

create table DeptEmp(
DeptNo number(2),
EmpNo  number(4));

insert into DeptEmp values(10,1001);
insert into DeptEmp values(10,1002);
insert into DeptEmp values(10,1003);
insert into DeptEmp values(20,2001);
insert into DeptEmp values(20,2002);
insert into DeptEmp values(20,2003);
insert into DeptEmp values(20,2004);
insert into DeptEmp values(20,2005);
insert into DeptEmp values(30,3001);
insert into DeptEmp values(30,3002);
insert into DeptEmp values(30,3003);
insert into DeptEmp values(30,3004);
insert into DeptEmp values(30,3005);
insert into DeptEmp values(30,3006);
commit;

SQL

--■■■decode関数と分析関数を組み合わせる方法■■■
select decode(EmpNO,min(EmpNO) over(partition by DeptNO),DeptNO) as DeptNO,
EmpNO,EmpCount,DeptCount
from(select DeptNO,EmpNO,
     decode(EmpNO,
     min(EmpNO) over(partition by DeptNO),
     count(EmpNO) over(partition by DeptNO)) as EmpCount,
     decode(Row_number() over(order by DeptNO,EmpNO),
     1,count(DeptNO) over()) as DeptCount
     from DeptEmp
     union all
     select distinct DeptNO,null,null,null
       from (select DeptNO,max(DeptNO) over() as maxDeptNO
               from DeptEmp)
      where DeptNO != maxDeptNO) a
order by a.DeptNO,EmpNO;

--■■■model句を使う方法(10g以降)■■■
select case Rn2 when 1 then DeptNO    end as DeptNO,EmpNO,
       case Rn2 when 1 then EmpCount  end as EmpCount,
       case Rn3 when 1 then DeptCount end as DeptCount
from (select DeptNO,EmpNO,Rn1,Rn2,Rn3,EmpCount,DeptCount
      from (select DeptNO,EmpNO,
            dense_rank() over(order by DeptNO desc) as Rn1
              from DeptEmp)
      model
      partition by(DeptNO)
      dimension by(Row_Number() over(partition by DeptNO order by EmpNO) as soeji)
      measures(EmpNO,Rn1,Row_Number() over(partition by DeptNO order by EmpNO) as Rn2,
               Row_Number() over(order by DeptNO,EmpNO) as Rn3,
               count(*) over(partition by DeptNO) as EmpCount,
               count(*) over() as DeptCount)
      rules(EmpNO[null] = null,
            Rn1[null] = Rn1[1])) a
 where Rn1 != 1 or EMPNO is not null
order by a.DeptNO,EmpNO;

解説

decode関数と分析関数を組み合わせてます。

---