日期:2014-05-17 浏览次数:20442 次
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-16 17:10:28
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tb_department]
if object_id('[tb_department]') is not null drop table [tb_department]
go
create table [tb_department]([id] int,[pid] int,[caption] varchar(2))
insert [tb_department]
select 1,0,'AA' union all
select 20,1,'BB' union all
select 64,20,'CC' union all
select 22,1,'DD' union all
select 23,22,'EE' union all
select 24,1,'FF' union all
select 25,0,'GG' union all
select 26,1,'HH' union all
select 27,25,'II'
--------------开始查询--------------------------
--CC,EE,FF,HH
;
WITH cte
AS ( SELECT * ,
1 [level]
FROM [tb_department]
WHERE [pid] = 0
AND caption = 'AA'
UNION ALL
SELECT a.id ,
&nb