--■■■union allを使う方法■■■
select dname,empno
from (select nvl2(empno,dname,'**') as dname,
nvl(empno,'**') as empno,
Row_Number() over(order by dname,empno desc nulls Last) as Rank,
count(*) over() as RecordCount
from (select dname,empno from emp
union all select distinct dname,null from emp))
where Rank != RecordCount
order by Rank;
--■■■rollupを使う方法1■■■
select dname,empno
from (select case grouping(empno) when 1 then '**' else dname end as dname,
case grouping(empno) when 1 then '**' else empno end as empno,
Row_Number() over(order by dname,grouping(empno),empno desc) as sortKey,
count(*) over() as maxSortKey
from emp
group by rollup(dname,empno)
having grouping_ID(dname,empno) != 3)
where sortKey != maxSortKey
order by sortKey;
--■■■rollupを使う方法2■■■
select dname,empno
from (select case grouping(empno) when 1 then '**' else dname end as dname,
case grouping(empno) when 1 then '**' else empno end as empno,
Row_Number() over(order by dname,grouping(empno),empno desc) as sortKey,
count(*) over() as maxSortKey
from emp
group by dname,rollup(empno))
where sortKey != maxSortKey
order by sortKey;
--■■■grouping setsを使う方法■■■
select dname,empno
from (select case grouping(empno) when 1 then '**' else dname end as dname,
case grouping(empno) when 1 then '**' else empno end as empno,
Row_Number() over(order by dname,grouping(empno),empno desc) as sortKey,
count(*) over() as maxSortKey
from emp
group by grouping sets((dname,empno),dname))
where sortKey != maxSortKey
order by sortKey;