SQL SERVER 2005 递归查询

/****** Object:  Table [dbo].[tblUserGroup]    Script Date: 11/24/2010 10:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblUserGroup](
 [UID] [varchar](64) NOT NULL,
 [GID] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblUserGroup] PRIMARY KEY CLUSTERED
(
 [UID] ASC,
 [GID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tblUserGroup] ([UID], [GID]) VALUES (N'u1', N'g1')
INSERT [dbo].[tblUserGroup] ([UID], [GID]) VALUES (N'u1', N'g2')
INSERT [dbo].[tblUserGroup] ([UID], [GID]) VALUES (N'u2', N'g3')
INSERT [dbo].[tblUserGroup] ([UID], [GID]) VALUES (N'u2', N'g4')
/****** Object:  Table [dbo].[tblGroupGroup]    Script Date: 11/24/2010 10:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblGroupGroup](
 [ID] [varchar](50) NOT NULL,
 [CID] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblGroupGroup] PRIMARY KEY CLUSTERED
(
 [ID] ASC,
 [CID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tblGroupGroup] ([ID], [CID]) VALUES (N'g1', N'g2')
INSERT [dbo].[tblGroupGroup] ([ID], [CID]) VALUES (N'g1', N'g5')
INSERT [dbo].[tblGroupGroup] ([ID], [CID]) VALUES (N'g2', N'g3')
INSERT [dbo].[tblGroupGroup] ([ID], [CID]) VALUES (N'g3', N'g4')
/****** Object:  Table [dbo].[tblUserInfo]    Script Date: 11/24/2010 10:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblUserInfo](
 [ID] [varchar](64) NOT NULL,
 CONSTRAINT [PK_tblUserInfo] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tblUserInfo] ([ID]) VALUES (N'u1')
INSERT [dbo].[tblUserInfo] ([ID]) VALUES (N'u2')
/****** Object:  Table [dbo].[tblGroup]    Script Date: 11/24/2010 10:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblGroup](
 [ID] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblGroup] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tblGroup] ([ID]) VALUES (N'g1')
INSERT [dbo].[tblGroup] ([ID]) VALUES (N'g2')
INSERT [dbo].[tblGroup] ([ID]) VALUES (N'g3')
INSERT [dbo].[tblGroup] ([ID]) VALUES (N'g4')
INSERT [dbo].[tblGroup] ([ID]) VALUES (N'g5')
/****** Object:  ForeignKey [FK_tblUserGroup_tblUserGroup]    Script Date: 11/24/2010 10:38:32 ******/
ALTER TABLE [dbo].[tblUserGroup]  WITH CHECK ADD  CONSTRAINT [FK_tblUserGroup_tblUserGroup] FOREIGN KEY([UID], [GID])
REFERENCES [dbo].[tblUserGroup] ([UID], [GID])
GO
ALTER TABLE [dbo].[tblUserGroup] CHECK CONSTRAINT [FK_tblUserGroup_tblUserGroup]
GO

--递归查询
with myT2 as(
select * from tblgroupgroup Where id in
(select tblgroupgroup.id from tblusergroup inner join tblgroupgroup on
tblgroupgroup.id=tblusergroup.gid where tblusergroup.uid='u1')
union all
select tblgroupgroup.* from myT2 inner join tblgroupgroup on myT2.cID=tblgroupgroup.ID)
select DISTINCT  id from myT2