SQL SERVER 2005 递归查询
来源:互联网 发布:C语言程序算法 pdf 编辑:程序博客网 时间:2024/05/13 03:27
/****** 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
- SQL SERVER 2005 递归查询
- SQL Server 递归查询
- sql server 递归查询
- sql server 递归查询
- sql server递归查询
- sql server 递归查询
- SQL Server递归查询
- SQL server递归查询
- sql server 2005 的递归查询示例
- SQL SERVER 2005的递归查询
- SQL SERVER 2005 递归查询备忘
- SQL SERVER 进行递归查询
- SQL SERVER 进行递归查询
- SQL Server中的递归查询
- Sql Server递归查询实例
- sql server递归分页查询
- SQL Server 2005中递归查询的写法
- SQL Server 2005中的CTE递归查询得到一棵树
- set_magic_quotes_runtime解释
- 谦哥的父亲
- 关闭或删除 MyeElipse 自带的 Derby 服务器
- 在ssh项目中hibernate的动态映射
- typedef用法 与#define
- SQL SERVER 2005 递归查询
- winform的FindControl
- t:jscookMenu t:panelNavigation2 及 t:jscookMenu不能够使用action跳转
- 高效人士的八个习惯
- 判断区域B是否在区域A内部的快速算法
- ubuntu下面无网络的情况下安装 emacs
- jsoup 1.4.1 发布,超棒的 HTML 解析器
- 如何使wicket中id不发生变化
- Openssl函数i2d_X509应该注意的地方