一个sql查询语句。该如何解决_Sql Server

一个sql查询语句。。。。。。。。
由于当初的设计初衷是可以群组包含群组，所以会出现这种情况，表的情况大致如下

ParentGroupId SubGroupId
1 2
2 3
3 4

现在我要查询出群组Id为1的群组所包含的所有群组Id，在上表中的查询结果就应该是2，3，4
不知查询语句怎样写，希望各位大虾不吝赐教，谢谢！

------解决方案--------------------

SQL code

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 [email protected]while @@rowcount>0begin   set @[email protected]+1   insert @T select A.ParentGroupId,A.SubGroupId,@lev from A,(select * from @T where [email protected]) 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 [email protected]
while @@rowcount>0
begin
  set @[email protected]+1
  insert @T select B.ParentGroupId,B.SubGroupId,@lev from B,(select * from @T where [email protected]) 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 [email protected]  
while @i <(select count(1) from A)
begin  
  set @[email protected]+1  
  set @[email protected]+1  
  insert @T select A.ParentGroupId,A.SubGroupId,@lev from A,(select * from @T where [email protected]) T where A.ParentGroupId=T.SubGroupId  
end  
select ParentGroupId,SubGroupId from @T  
go  

exec Select_A  'b'