sql server 2000 递归显示数据

本文介绍了一个包含用户、用户组及它们之间关联关系的数据库设计方案。该方案通过多个表(如用户信息表、用户组表等)来组织数据,并利用外键约束确保数据一致性。此外,还提供了一个递归函数用于获取用户所属的所有子单位。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

USE [T]
GO
/****** Object:  Table [dbo].[tblUserGroup]    Script Date: 11/30/2010 11:14:18 ******/
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'g2')
INSERT [dbo].[tblUserGroup] ([UID], [GID]) VALUES (N'u1', N'g3')
INSERT [dbo].[tblUserGroup] ([UID], [GID]) VALUES (N'u2', N'g5')
/****** Object:  Table [dbo].[tblGroupGroup]    Script Date: 11/30/2010 11:14:18 ******/
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'g5')
INSERT [dbo].[tblGroupGroup] ([ID], [CID]) VALUES (N'g2', N'g1')
INSERT [dbo].[tblGroupGroup] ([ID], [CID]) VALUES (N'g5', N'g2')
INSERT [dbo].[tblGroupGroup] ([ID], [CID]) VALUES (N'g5', N'g3')
/****** Object:  Table [dbo].[tblUserInfo]    Script Date: 11/30/2010 11:14:18 ******/
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/30/2010 11:14:18 ******/
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:  UserDefinedFunction [dbo].[GetChildUnit]    Script Date: 11/30/2010 11:14:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[GetChildUnit](@id varchar(50), @Level int)
Returns @t table(id varchar(50),cid varchar(50),Level int)
As
Begin
 if @Level>=32
  return
 declare @tt table(id varchar(50),cid varchar(50),l int)
 set @Level=@Level+1
 if @Level=1
  insert into @tt select tblgroupgroup.*,@Level from tblusergroup inner join tblgroupgroup on id=gid where uid=@id
 else
  insert into @tt select *,@Level from tblgroupgroup where id=@id
 insert into @t select * from @tt
 
 DECLARE @cid varchar(50)
  DECLARE cc CURSOR FOR select cid from @tt
 open cc
  FETCH NEXT FROM cc INTO @cid
  WHILE @@FETCH_STATUS = 0
  begin
   insert into @t select * from GetChildUnit(@cid,@Level)
   FETCH NEXT FROM cc INTO @cid
  end
 close cc
 DEALLOCATE cc
 
    Return
End
GO
/****** Object:  ForeignKey [FK_tblUserGroup_tblUserGroup]    Script Date: 11/30/2010 11:14:18 ******/
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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值