;with f as ( select * from tb where uid=4 union all select a.* from tb a, f where a.uid=f.pid )select uid from f
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-05-04 10:49:32 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([uid] int,[name] varchar(1),[pid] int) insert [tb] select 1,'a',0 union all select 2,'b',1 union all select 3,'c',2 union all select 4,'d',3 union all select 5,'e',0 union all select 6,'f',5 --------------开始查询-------------------------- ;with f as ( select * from tb where uid=4 union all select a.* from tb a, f where a.uid=f.pid )
select uid from f where uid<>4 order by 1 ----------------结果---------------------------- /* uid ----------- 1 2 3(3 行受影响)*/
(
select * from tb where uid=4
union all
select a.* from tb a, f where a.uid=f.pid
)select uid from f
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-05-04 10:49:32
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([uid] int,[name] varchar(1),[pid] int)
insert [tb]
select 1,'a',0 union all
select 2,'b',1 union all
select 3,'c',2 union all
select 4,'d',3 union all
select 5,'e',0 union all
select 6,'f',5
--------------开始查询--------------------------
;with f as
(
select * from tb where uid=4
union all
select a.* from tb a, f where a.uid=f.pid
)
select uid from f where uid<>4 order by 1
----------------结果----------------------------
/* uid
-----------
1
2
3(3 行受影响)*/