/********************************************************************************************
-- Title : [2k] 상관 관계 하위 쿼리 셈플 및 변환
-- Reference : dbrang.tistory.com
-- Key word : 상관 관계 하위 쿼리 질의 corelation
********************************************************************************************/
-- Sample 1.(Pubs DB에서)
use pubs
go
select stor_id, title_id, qty from sales s1
where qty = (select MAX(qty) from sales s2
where s1.stor_id = s2.stor_id)
order by stor_id, title_id, qty
/*
select stor_id, title_id, qty from sales s1 where qty =
|--Sort(ORDER BY:([s1].[stor_id] ASC, [s1].[title_id] A
|--Nested Loops(Inner Join, OUTER REFERENCES:([s2]
|--Stream Aggregate(GROUP BY:([s2].[stor_id])
| |--Clustered Index Scan(OBJECT:([pubs].[
|--Clustered Index Seek(OBJECT:([pubs].[dbo].
*/
select a.stor_id, a.title_id, a.qty
from sales a
inner join (select stor_id, max(qty) max_q
from sales
group by stor_id
) b
on a.stor_id = b.stor_id
and a.qty = b.max_q
order by a.stor_id, a.title_id, a.qty
/*
select a.stor_id, a.title_id, a.qty from sales a inner
|--Sort(ORDER BY:([a].[stor_id] ASC, [a].[title_id] ASC
|--Nested Loops(Inner Join, OUTER REFERENCES:([sal
|--Stream Aggregate(GROUP BY:([sales].[stor_i
| |--Clustered Index Scan(OBJECT:([pubs].[
|--Clustered Index Seek(OBJECT:([pubs].[dbo].
*/
-- Sample 2.(한국해운조합에서)
select a.*
from tsendors a
where a.dept_br = '020'
and a.pol_no IN (select z.pol_no
from tsendors Z
where z.pol_no = a.pol_no
and z.prod_cd = '300'
and z.cot_stat in ('0','4') --2007.07.14 황승주 수정 만기정산도 정상적인것으로 처리
and z.endrs_no = (select max(Y.endrs_no)
from tsendors Y
where Y.pol_no = z.pol_no
and Y.endrs_YMD <= '20081016'--@i_basic_ymd
)
)
select a.*
from tsendors a
inner join (select pol_no, endrs_no
from tsendors
where prod_cd = '300'
and cot_stat in ('0','4')
group by pol_no, endrs_no
) b
on a.pol_no = b.pol_no
inner join (select pol_no, max(endrs_no) m_endrs_no
from tsendors
where endrs_ymd < '20081016'
group by pol_no
) c
on b.pol_no = c.pol_no
and b.endrs_no = c.m_endrs_no
where a.dept_br = '020'
-- 실행 계획은 똑같지만 어느것에서는 상관 관계 하위 쿼리가 더 나쁠 수 있당...