|
with t1 as
(
select '인도네시아' as nat_name
, 'Banyu' as project_name
, 'Staff' as gubun
, 11 as prev
, 11 as curr
, 0 as diff
, 1 as nat_sortorder
, 1 as project_sortorder
, 1 as gubun_sortorder
from dual
union all
select '인도네시아' as nat_name
, 'Banyu' as project_name
, 'Worker' as gubun
, 11 as prev
, 11 as curr
, 0 as diff
, 1 as nat_sortorder
, 1 as project_sortorder
, 2 as gubun_sortorder
from dual
union all
select '인도네시아' as nat_name
, 'Senoro' as project_name
, 'Staff' as gubun
, 10 as prev
, 10 as curr
, 0 as diff
, 1 as nat_sortorder
, 2 as project_sortorder
, 1 as gubun_sortorder
from dual
union all
select '멕시코' as nat_name
, 'Manzanillo' as project_name
, 'Staff' as gubun
, 2 as prev
, 2 as curr
, 0 as diff
, 2 as nat_sortorder
, 1 as project_sortorder
, 1 as gubun_sortorder
from dual
union all
select '멕시코' as nat_name
, 'Manzanillo' as project_name
, 'Worker' as gubun
, 3 as prev
, 2 as curr
, -1 as diff
, 2 as nat_sortorder
, 1 as project_sortorder
, 2 as gubun_sortorder
from dual
union all
select '멕시코' as nat_name
, 'Norte II' as project_name
, 'Staff' as gubun
, 5 as prev
, 4 as curr
, -1 as diff
, 2 as nat_sortorder
, 2 as project_sortorder
, 1 as gubun_sortorder
from dual
union all
select '멕시코' as nat_name
, 'Norte II' as project_name
, 'Worker' as gubun
, 3 as prev
, 3 as curr
, 0 as diff
, 2 as nat_sortorder
, 1 as project_sortorder
, 2 as gubun_sortorder
from dual
)
select *
from t1
order by nat_sortorder, project_sortorder, gubun_sortorder
상기의 데이터로
하기와 같은 모양으로 나와야 합니다
모양은 나오는데 Sorting이 문제네요
nat_sortorder, project_sortorder, gubun_sortorder 이것으로 소팅을 해야하고
그밑에 소계, 맨 끝에 총계가 나와야하는데요.....
구분별로 "Total"
프로젝트명에 각 국가별로 "Sub-Total"이라는 명칭으로
NVL("국가", 'Total') "국가"
,NVL2("구분", NVL("프로젝트명", 'Sub-Total'), 'Sub-Total') "프로젝트명
,구분
,SUM(금주) AS 금주
,SUM(전주) AS 전주
,SUM(차이) AS 차이
GROUP BY GROUPING SETS ( ROLLUP("국가", "구분", "프로젝트명"), "구분")
위에 같은 방법을 써서 얼추 나오긴 하는데... Sorting 에서 막히네요
좋은방법이 있을까요?
----------------------------------------------------------------------
국가 프로젝트명 구분 지난주 금주 차이
---------------------------------------------------------------------
인도네시아 Banyu Total 11 11 0
Staff 11 11 0
Senoro Total 10 10 0
Staff 10 10 0
Sub-Total Sub-Total 21 21 0
Staff 21 21 0
Worker 0 0 0
----------------------------------------------------------------------
멕시코 Manzanillo Total 5 4 -1
Staff 2 2 0
Worker 3 2 -1
Norte II Total 8 7 -1
Staff 5 4 -1
Worker 3 3 0
Sub-Total Sub-Total 13 11 -2
Staff 7 6 -1
Worker 6 5 -1
----------------------------------------------------------------------
Total Total 34 32 -2
Staff 28 27 -1
Worker 6 5 -1
----------------------------------------------------------------------
|
