一个sql查询语句。。。。。。。。
由于当初的设计初衷是可以群组包含群组，所以会出现这种情况，表的情况大致如下
ParentGroupId SubGroupId
1 2
2 3
3 4
现在我要查询出群组Id为1的群组所包含的所有群组Id，在上表中的查询结果就应该是2，3，4
不知查询语句怎样写，希望各位大虾不吝赐教，谢谢！
[解决办法]
Create Table A( ParentGroupId int , SubGroupId int)insert A select 1,2insert A select 2,3insert A select 3,4gocreate Proc Select_A(@ParentGroupId int)as declare @lev intdeclare @T table(ParentGroupId int,SubGroupId int,lev int)set @lev=1insert @T select ParentGroupId,SubGroupId,@lev from A where ParentGroupId=@ParentGroupIdwhile @@rowcount>0begin set @lev=@lev+1 insert @T select A.ParentGroupId,A.SubGroupId,@lev from A,(select * from @T where lev=@lev-1) T where A.ParentGroupId=T.SubGroupIdendselect ParentGroupId,SubGroupId from @Tgoexec Select_A 2
[解决办法]
Create Table B
(
ParentGroupId nvarchar ,
SubGroupId nvarchar
)
insert B select '1','2'
insert B select '2','3'
insert B select '3','4'
go
alter Proc Select_B(@ParentGroupId nvarchar )
as
declare @lev int
declare @T table(ParentGroupId char,SubGroupId char,lev int)
set @lev=1
insert @T select ParentGroupId,SubGroupId,@lev from B where ParentGroupId=@ParentGroupId
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select B.ParentGroupId,B.SubGroupId,@lev from B,(select * from @T where lev=@lev-1) T where B.ParentGroupId=T.SubGroupId
end
select ParentGroupId,SubGroupId from @T
go
exec Select_B '1'
[解决办法]
Create Table A
(
ParentGroupId nvarchar ,
SubGroupId nvarchar
)
insert A select 'a', 'b'
insert A select 'b', 'c'
insert A select 'c', 'd'
go
Create Proc Select_A(@ParentGroupId nvarchar )
as
declare @lev int
declare @i int
declare @T table(ParentGroupId nvarchar,SubGroupId nvarchar,lev int)
set @lev=1
set @i=1
insert @T select ParentGroupId,SubGroupId,@lev from A where ParentGroupId=@ParentGroupId
while @i <(select count(1) from A)
begin
set @lev=@lev+1
set @i=@i+1
insert @T select A.ParentGroupId,A.SubGroupId,@lev from A,(select * from @T where lev=@lev-1) T where A.ParentGroupId=T.SubGroupId
end
select ParentGroupId,SubGroupId from @T
go
exec Select_A 'b'
