近期被交辦的一個作業
本想使用遞迴，但好像找不出個規則，
只好改用補資料的方式，串出交集，再取聯集中最大的一筆
比較難的部份是B、D的交集，弄了好久
- 第一階，先取得AC、AB、CD的關聯
- 第二階，再取得ABC、ACD
- 第三階，最大集合ABCD
--建資料
if OBJECT_ID('tempdb..#T') is not null
drop table #T
create table #T (ID varchar(10),TEL varchar(10))
insert into #T(ID,TEL)
select 'A','123'
union select 'A','124'
union select 'B','124'
union select 'B','125'
union select 'C','123'
union select 'C','126'
union select 'D','126'
union select 'D','127'
union select 'E','128'
union select 'E','129'
union select 'F','130'
union select 'F','131'
union select 'G','131'
union select 'G','132'
select * from #T
-------------------------------------------------------------
;WITH TAB (ID, LV) as --第一階
(
select A.ID,case when B.ID is null then A.ID else B.ID end as LV
from #T A
left join #T B
on A.TEL = B.TEL
and B.ID <> A.ID
)
,RESULT as --第二、三階
(
select A.ID,A.LV,B.LV as LV1,C.LV as LV2
from TAB A
left join TAB B
on A.LV=B.ID
and A.ID<>B.ID
left join TAB C
on C.ID=B.LV
and A.ID<>C.ID
)
,RESULT2 as --三階聯集取最大
(
select *,ROW_NUMBER() over(partition by ID order by LV desc) sn
from (
select ID,LV from RESULT
union
select ID,LV1 from RESULT
union
select ID,LV2 from RESULT
) T
)
select * from RESULT2 where sn=1
