트리 구조 쿼리

다소 복잡함. 한번에 눈에 잘 안들어 온다.

카페과 그 카페를 관리하는 관리자 정보가 한 테이블에 존재하고 이를 트리 형태로 출력하는 쿼리.

부가 정보은 주소 전화 번호 포함.

/*

with netcafe(Parent_Code, NetCafe_Code, NetCafe_Name1, NetCafe_Tel, levels, sort)

as

(

select Parent_Code, NetCafe_Code, NetCafe_Name1, NetCafe_Tel, 1 as levels, convert(varchar, isnull(Parent_Code,'') + ' ' +  NetCafe_Code) as sort

from dbo.NetCafe_Info_Base (nolock)

where Parent_Code is null

union all

select a.Parent_Code, a.NetCafe_Code, a.NetCafe_Name1, a.NetCafe_Tel,  levels+1 as levels, convert(varchar, rtrim(sort) + ' | ' + isnull(a.Parent_Code,'') + ' ' +  a.NetCafe_Code) as sort

from dbo.NetCafe_Info_Base a, netcafe b

where a.Parent_Code = b.NetCafe_Code

)

select Parent_Code, AA.NetCafe_Code, NetCafe_Name1, NetCafe_Tel, levels, thisuse from netcafe AA

left outer join

(

SELECT distinct [NetCafe_Code]

      , 'O' as thisuse

  FROM [NetCafeAdminDB].[dbo].[NetCafe_Info_Bill] (nolock)

  where [Bill_End_Date] is null or [Bill_End_Date] > '20140131'

) BB

on AA.NetCafe_Code = BB.NetCafe_Code

order by sort

*/

with netcafe(Parent_Code, NetCafe_Code, NetCafe_Name1, NetCafe_Tel, Zip_Code1, Zip_Code2, NetCafe_Addr1,NetCafe_Addr2, NetCafe_Admin_Name, NetCafe_Admin_Tel, levels, sort)

as

(

select Parent_Code, NetCafe_Code, NetCafe_Name1, NetCafe_Tel, Zip_Code1, Zip_Code2, NetCafe_Addr1,NetCafe_Addr2, NetCafe_Admin_Name, NetCafe_Admin_Tel, 1 as levels, convert(varchar, isnull(Parent_Code,'') + ' ' +  NetCafe_Code) as sort from

(select Parent_Code, NetCafe_Code, NetCafe_Name1, NetCafe_Tel, Zip_Code1, Zip_Code2, NetCafe_Addr1,NetCafe_Addr2, NetCafe_Admin_Name, NetCafe_Admin_Tel from dbo.NetCafe_Info_Base (nolock)

where

NetCafe_Code in (

 SELECT [NetCafe_Code]

 FROM [NetCafeAdminDB].[dbo].[NetCafe_Info_Bill] (nolock)

 where [Bill_End_Date] is null or [Bill_End_Date] > '20140131')

or

NetCafe_Code in (select Parent_Code from dbo.NetCafe_Info_Base (nolock)

where NetCafe_Code in (

SELECT [NetCafe_Code]

 FROM [NetCafeAdminDB].[dbo].[NetCafe_Info_Bill] (nolock)

 where [Bill_End_Date] is null or [Bill_End_Date] > '20140131'))

) MASTERS

where Parent_Code is null

union all

select a.Parent_Code, a.NetCafe_Code, a.NetCafe_Name1, a.NetCafe_Tel, a.Zip_Code1, a.Zip_Code2, a.NetCafe_Addr1, a.NetCafe_Addr2, a.NetCafe_Admin_Name, a.NetCafe_Admin_Tel, levels+1 as levels, convert(varchar, rtrim(sort) + ' | ' + isnull(a.Parent_Code,'') + ' ' +  a.NetCafe_Code) as sort

from (select Parent_Code, NetCafe_Code, NetCafe_Name1, NetCafe_Tel, Zip_Code1, Zip_Code2, NetCafe_Addr1,NetCafe_Addr2, NetCafe_Admin_Name, NetCafe_Admin_Tel from dbo.NetCafe_Info_Base (nolock)

where

NetCafe_Code in (

 SELECT [NetCafe_Code]

 FROM [NetCafeAdminDB].[dbo].[NetCafe_Info_Bill] (nolock)

 where [Bill_End_Date] is null or [Bill_End_Date] > '20140131')

or

NetCafe_Code in (select Parent_Code from dbo.NetCafe_Info_Base (nolock)

where NetCafe_Code in (

SELECT [NetCafe_Code]

 FROM [NetCafeAdminDB].[dbo].[NetCafe_Info_Bill] (nolock)

 where [Bill_End_Date] is null or [Bill_End_Date] > '20140131'))

) a, netcafe b

where a.Parent_Code = b.NetCafe_Code

)

select Parent_Code, AA.NetCafe_Code, NetCafe_Name1, NetCafe_Tel, Zip_Code1, Zip_Code2, NetCafe_Addr1,NetCafe_Addr2, NetCafe_Admin_Name, levels from netcafe AA

order by sort