请教个问题 如下:
原始单个表中数据
ID FID
1 0
2 1
3 2
4 0
5 3
6 4
7 5
8 6查询要显示的结果 通过 where ID=7
ID FID
1 0
2 1
3 2
5 3
7 5谢谢
原始单个表中数据
ID FID
1 0
2 1
3 2
4 0
5 3
6 4
7 5
8 6查询要显示的结果 通过 where ID=7
ID FID
1 0
2 1
3 2
5 3
7 5谢谢
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-16 16:09:59
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[FID] NVARCHAR(10))
INSERT [tb]
SELECT 1,'0' UNION ALL
SELECT 2,'1' UNION ALL
SELECT 3,'2' UNION ALL
SELECT 4,'0' UNION ALL
SELECT 5,'3' UNION ALL
SELECT 6,'4' UNION ALL
SELECT 7,'5' UNION ALL
SELECT 8,'6'
GO
--SELECT * FROM [tb]-->SQL查询如下:
;with t as
(
select * from tb where ID=7
union all
select a.* from tb a join t on a.ID=t.FID
)
select * from t order by 1
/*
ID FID
----------- ----------
1 0
2 1
3 2
5 3
7 5(5 行受影响)*/
drop table tb
Go
Create table tb([ID] int,[FID] int)
Insert tb
select 1,0 union all
select 2,1 union all
select 3,2 union all
select 4,0 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,6
Go
;with cte as
(select id,fid from tb where id=7
union all
select b.id,b.fid from tb b
inner join cte c on c.fid=b.id)
select * from cte order by id,fid
/*
id fid
----------- -----------
1 0
2 1
3 2
5 3
7 5(5 個資料列受到影響)
*/