다소 복잡함. 한번에 눈에 잘 안들어 온다.
카페과 그 카페를 관리하는 관리자 정보가 한 테이블에 존재하고 이를 트리 형태로 출력하는 쿼리.
부가 정보은 주소 전화 번호 포함.
/*
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
'MS-SQL' 카테고리의 다른 글
DB 마이그레이션 후... (0) | 2013.10.28 |
---|---|
DB의 전체 테이블 row 확인하기 (0) | 2013.07.10 |
MS-SQL Restore시 1354번 에러 (0) | 2012.06.26 |
Linked DB 생성하기 (0) | 2012.06.08 |
백업 파일로 리스토어 할 때 에러가 나면... (0) | 2012.04.26 |