/********************************************************************************************
-- Title : [2k] CROSS JOIN을 활용한 집계 처리 예
-- Reference : dbrang.tistory.com
-- Key word : cross join 중간 집계 union all 가상 집합 rollup
********************************************************************************************/
use tempdb
go
drop table ttt
go
create table ttt
(no int, name varchar(100), kor int, eng int, math int)
go
insert into ttt
select 1, '박명수', 60, 70, 40 union all
select 2, '유재석', 80, 90, 90 union all
select 3, '노홍철', 40, 30, 50 union all
select 4, '정준하', 60, 70, 50 union all
select 5, '정형돈', 90, 90, 80
go
select * from ttt
go
-- 임시 집합 생성 및 조인
select b.x, a.no, a.name, a.kor, a.eng, a.math, a.kor+a.eng+a.math "sum"
from ttt a
cross join (select 1 "x" union all select 2 "x") b
order by b.x, a.no
go
-- 집합별 처리
select
case when x = 2 then 9999999 else a.no end "no"
, case when x = 2 then '합계' else a.name end "name"
, a.kor "kor"
, a.eng "eng"
, a.math "math"
, a.kor+a.eng+a.math "tot"
from ttt a
cross join (select 1 "x" union all select 2 "x") b
order by 1
-- 집합 조합(Case. 1)
select g.no "no", g.name "name", sum(g.kor) "kor", sum(g.eng) "eng"
, sum(g.math) "math"
, sum(g.tot) "tot"
from(
select
case when x = 2 then 9999999 else a.no end "no"
, case when x = 2 then '합계' else a.name end "name"
, a.kor "kor"
, a.eng "eng"
, a.math "math"
, a.kor+a.eng+a.math "tot"
from ttt a
cross join (select 1 "x" union all select 2 "x") b
) g
group by g.no, g.name
order by g.no
go
-- 집합 조합(Case. 2)
-- 이 경우가 다중 중간 집합 계산에 좋다.
select
case when x = 2 then 9999999 else a.no end "no"
, case when x = 2 then '합계' else a.name end "name"
, sum(a.kor) "kor"
, sum(a.eng) "eng"
, sum(a.math) "math"
, sum(a.kor+a.eng+a.math) "tot"
from ttt a
cross join (select 1 "x" union all select 2 "x") b
group by (case when b.x = 2 then 9999999 else a.no end)
, (case when b.x = 2 then '합계' else a.name end)
, (case when b.x = 2 then 'grp2' end)
, (case when b.x = 2 then 'grp2' end)
, (case when b.x = 2 then 'grp2' end)
go
-- 집합 조합(Case. 3)
-- 컬럼 하나하나 핸들링이 어렵다.
select no "no", name "name", sum(kor) "kor", sum(eng) "eng"
, sum(math) "math"
, sum(kor+eng+math) "tot"
from ttt
group by no, name
with rollup
having grouping(no) = 1
or grouping(name) = 0
-- 한국00조합 샘플
SELECT *
FROM
(
SELECT CASE bb.x WHEN 2 THEN '합계' ELSE aa.pol_no END "pol_no"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.sailor_nm END "sailor_nm"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.vessel_nm END "vessel_nm"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.cust_kor END "cust_kor"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.hull_typ END "hull_typ"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.hull_typ_nm END "hull_typ_nm"
, AVG(aa.tot_ton) "tot_ton"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.nati_cd END "nati_cd"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.nati_nm END "nati_nm"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.jik_cd END "jik_cd"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.jik_cd_nm END "jik_cd_nm"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.sailor_no END "sailor_no"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.ins_strt_ymd END "ins_strt_ymd"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.wrk_ymd_fm END "wrk_ymd_fm"
, CASE bb.x WHEN 2 THEN NULL ELSE aa.wrk_ymd_to END "wrk_ymd_to"
, SUM(aa.sailor_avr_pay) "sailor_avr_pay"
FROM
(
SELECT dbo.FN_POL_NO(a.pol_no) pol_no,
s.sailor_nm,
c.vessel_nm,
b.cust_kor,
c.hull_typ,
ISNULL(k1.hull_typ_nm,'') hull_typ_nm,
c.tot_ton,
s.nati_cd,
ISNULL(k3.nati_nm,'') nati_nm,
s.jik_cd,
ISNULL(k2.jik_cd_nm,'') jik_cd_nm,
eksadb.dbo.FN_FORMAT(s.sailor_no, 2) sailor_no,
eksadb.dbo.FN_DATE(a.ins_strt_ymd,'/') ins_strt_ymd,
eksadb.dbo.FN_DATE(s.wrk_ymd_fm,'/') wrk_ymd_fm,
eksadb.dbo.FN_DATE(s.wrk_ymd_to,'/') wrk_ymd_to,
s.sailor_avr_pay
FROM tscotbas a
INNER JOIN tscotrel b ON a.pol_no = b.pol_no AND b.cont_yn = 'Y'
INNER JOIN tsvessel c ON a.pol_no = c.pol_no
INNER JOIN tsendors d ON a.pol_no = d.pol_no
--INNER JOIN tscotbase e ON a.pol_no = e.pol_no AND a.endrs_no = e.endrs_no
INNER JOIN (
SELECT *
FROM tssailor ts
WHERE @i_basic_ymd BETWEEN wrk_ymd_fm AND wrk_ymd_to
AND sailor_nm LIKE @i_sailor_nm + '%'
AND nati_cd LIKE @i_nati_cd + '%'
AND EXISTS (
SELECT pol_no
FROM (
select pol_no, max(endrs_no) endrs_no
from tssailor
WHERE endrs_ymd <= @i_basic_ymd
AND sailor_nm LIKE @i_sailor_nm + '%'
AND nati_cd LIKE @i_nati_cd + '%'
GROUP by pol_no
) tb
WHERE ts.pol_no = tb.pol_no
AND ts.endrs_no = tb.endrs_no
)
) s ON a.pol_no = s.pol_no
LEFT OUTER JOIN (
SELECT glbd_code, glbd_cdnm hull_typ_nm FROM eksadb.dbo.hsgc_glbd
WHERE glbd_idcd = 'A001'
) k1 ON c.hull_typ = k1.glbd_code
LEFT OUTER JOIN (
SELECT glbd_code, glbd_cdnm jik_cd_nm FROM eksadb.dbo.hsgc_glbd
WHERE glbd_idcd = 'A037'
) k2 ON s.jik_cd = k2.glbd_code
LEFT OUTER JOIN (
SELECT glbd_code, glbd_cdnm nati_nm FROM eksadb.dbo.hsgc_glbd
WHERE glbd_idcd = 'A038'
) k3 ON s.nati_cd = k3.glbd_code
WHERE d.prod_cd = '300'
AND d.real_cot_yn = '1' -- 진계약여부(1:진계약)
AND d.cot_stat in('0','4') -- 계약상태(0:정상)
AND a.endrs_fin_ymd = '20500101'
AND b.endrs_fin_ymd = '20500101'
AND c.endrs_fin_ymd = '20500101'
AND d.endrs_fin_ymd = '20500101'
AND @i_basic_ymd BETWEEN a.ins_strt_ymd AND a.ins_fin_ymd -- 기준일자
AND a.cot_dept_br LIKE @i_dept_br + '%' -- 지부
AND c.hull_typ LIKE @i_hull_typ + '%' -- 선종
AND a.pol_no LIKE @i_pol_no + '%' -- 증권번호
AND b.cust_kor LIKE @i_cust_kor + '%' -- 계약자명
AND c.vessel_nm LIKE @i_vessel_nm + '%' -- 선박명
) aa
CROSS JOIN
(SELECT 1 "x" UNION ALL
SELECT 2
) bb
GROUP BY CASE bb.x WHEN 2 THEN '합계' ELSE aa.pol_no END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.sailor_nm END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.vessel_nm END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.cust_kor END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.hull_typ END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.hull_typ_nm END
--, CASE bb.x WHEN 2 THEN NULL ELSE aa.tot_ton END
, CASE bb.x WHEN 2 THEN 'CAL' ELSE 'PER' END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.nati_cd END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.nati_nm END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.jik_cd END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.jik_cd_nm END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.sailor_no END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.ins_strt_ymd END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.wrk_ymd_fm END
, CASE bb.x WHEN 2 THEN NULL ELSE aa.wrk_ymd_to END
, CASE bb.x WHEN 2 THEN 'CAL' ELSE 'PER' END
) ccc
ORDER BY CASE WHEN ccc.pol_no = '합계' THEN '000'
ELSE ccc.pol_no
END