数据源: 后三条数据的父ID为105,即是上一组数据
cniStraResolveID cniYear cniPreStraResolveID cniIndicatorID cnfGoalValue
---------------- ----------- ------------------- -------------- ----------------------
105 2010 0 14 33
105 2010 0 13 33
105 2010 0 24 33
105 2010 0 23 33
105 2010 0 22 33
105 2010 0 19 33
108 2010 105 14 1
108 2010 105 13 1
108 2010 105 24 1
108 2010 105 25 1
得到的结果是:
cniYear cniPreStraResolveID cniIndicatorID cnfGoalValue cnfGoalValue
----------- ------------------- -------------- ------------- ---------
2010 0 14 33 1
2010 0 13 33 1
2010 0 24 33 1
2010 0 23 33
2010 0 22 33
2010 0 19 33
2010 105 25 1
cniStraResolveID cniYear cniPreStraResolveID cniIndicatorID cnfGoalValue
---------------- ----------- ------------------- -------------- ----------------------
105 2010 0 14 33
105 2010 0 13 33
105 2010 0 24 33
105 2010 0 23 33
105 2010 0 22 33
105 2010 0 19 33
108 2010 105 14 1
108 2010 105 13 1
108 2010 105 24 1
108 2010 105 25 1
得到的结果是:
cniYear cniPreStraResolveID cniIndicatorID cnfGoalValue cnfGoalValue
----------- ------------------- -------------- ------------- ---------
2010 0 14 33 1
2010 0 13 33 1
2010 0 24 33 1
2010 0 23 33
2010 0 22 33
2010 0 19 33
2010 105 25 1
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-10 14:00:24
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([cniStraResolveID] int,[cniYear] int,[cniPreStraResolveID] int,[cniIndicatorID] int,[cnfGoalValue] int)
insert [tb]
select 105,2010,0,14,33 union all
select 105,2010,0,13,33 union all
select 105,2010,0,24,33 union all
select 105,2010,0,23,33 union all
select 105,2010,0,22,33 union all
select 105,2010,0,19,33 union all
select 108,2010,105,14,1 union all
select 108,2010,105,13,1 union all
select 108,2010,105,24,1 union all
select 108,2010,105,25,1
--------------开始查询--------------------------
DECLARE @cniStraResolveID nvarchar(20)
SET @cniStraResolveID = 105
;WITH
f AS(
-- 定位点成员
SELECT * FROM tb
WHERE cniStraResolveID =@cniStraResolveID
UNION ALL
SELECT A.*
FROM tb A, f B
WHERE A.cniStraResolveID = B.cniPreStraResolveID
)
SELECT * FROM f
GO----------------结果----------------------------
/* cniStraResolveID cniYear cniPreStraResolveID cniIndicatorID cnfGoalValue
---------------- ----------- ------------------- -------------- ------------
105 2010 0 14 33
105 2010 0 13 33
105 2010 0 24 33
105 2010 0 23 33
105 2010 0 22 33
105 2010 0 19 33(6 行受影响)*/
From t1
Left Join (selet * from t1 where xxx) as t2 On(t1.xx = t2.xx)
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/
go
create table tb(cniStraResolveID int
,cniYear int
,cniPreStraResolveID int
,cniIndicatorID int
,cnfGoalValue int)insert tb select 105,2010,0,14,33
union all select 105,2010,0,13,33
union all select 105,2010,0,24,33
union all select 105,2010,0,23,33
union all select 105,2010,0,22,33
union all select 105,2010,0,19,33
union all select 108,2010,105,14,1
union all select 108,2010,105,13,1
union all select 108,2010,105,24,1
union all select 108,2010,105,25,1 select cniYear=isnull(a.cniYear,b.cniYear)
,cniPreStraResolveID=isnull(a.cniPreStraResolveID,b.cniPreStraResolveID)
,cniIndicatorID=isnull(a.cniIndicatorID,b.cniIndicatorID)
,a.cnfGoalValue
,b.cnfGoalValue
from (select * from tb where cniStraResolveID=105) a
full join (select * from tb where cniPreStraResolveID=105) b
on a.cniIndicatorID=b.cniIndicatorID
/*(10 行受影响)
cniYear cniPreStraResolveID cniIndicatorID cnfGoalValue cnfGoalValue
----------- ------------------- -------------- ------------ ------------
2010 0 14 33 1
2010 0 13 33 1
2010 0 24 33 1
2010 0 23 33 NULL
2010 0 22 33 NULL
2010 0 19 33 NULL
2010 105 25 NULL 1(7 行受影响)
*/