34,590
社区成员
发帖
与我相关
我的任务
分享
--创建表
/*==============================================================*/
/* Table: DeptBase */
/*==============================================================*/
create table DeptBase (
DeptID int identity,
BaseDeptID int null,
DeptName nvarchar(50) null,
DeptText nvarchar(50) not null,
DeptTel nvarchar(50) null,
IsForbid bit not null,
constraint PK_DEPTBASE primary key (DeptID)
)
go
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'部门表',
'user', @CurrentUser, 'table', 'DeptBase'
go
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'部门ID',
'user', @CurrentUser, 'table', 'DeptBase', 'column', 'DeptID'
go
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'上级部门ID',
'user', @CurrentUser, 'table', 'DeptBase', 'column', 'BaseDeptID'
go
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'部门电话',
'user', @CurrentUser, 'table', 'DeptBase', 'column', 'DeptTel'
go
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
;with cte as(
select deptid,basedeptid,convert(nvarchar(100),Depttext)Depttext
from deptbase a
where exists(select 1 from deptbase where basedeptid=a.basedeptid and deptid=40)
union all
select a.deptid,a.basedeptid,convert(nvarchar(100),a.Depttext+' '+b.Depttext) Depttext from deptbase a inner join cte b on a.deptid=b.basedeptid
)select Depttext from cte where basedeptid is null
/*
Depttext
----------------------------------------------------------------------------------------------------
福建省 福州市 罗源县
福建省 福州市 连江县
福建省 福州市 闽清县
福建省 福州市 平潭县
福建省 福州市 闽侯县
福建省 福州市 长乐市
福建省 福州市 福清市
福建省 福州市 马尾区
福建省 福州市 晋安区
福建省 福州市 仓山区
福建省 福州市 台江区
福建省 福州市 鼓楼区
(12 行受影响)
*/
go
;with cte as(
select deptid,basedeptid,convert(nvarchar(100),Depttext)Depttext from deptbase where deptid=40
union all
select a.deptid,a.basedeptid,convert(nvarchar(100),a.Depttext+' '+b.Depttext) Depttext from deptbase a inner join cte b on a.deptid=b.basedeptid
)select Depttext from cte where basedeptid is null
/*
Depttext
----------------------------------------------------------------------------------------------------
福建省 福州市 鼓楼区
(1 行受影响)
*/
go
select a.DeptText+' '+b.Depttext+' '+c.depttext
from DeptBase a inner join DeptBase b on a.deptid=b.basedeptid
inner join DeptBase c on b.deptid=c.basedeptid
where c.deptid=40
/*
------------------------------------------------------------------------------------------------------------------------------------------------------
福建省 福州市 鼓楼区
(1 行受影响)
*/
go
select a.DeptText+b.Depttext+c.depttext
from DeptBase a inner join DeptBase b on a.deptid=b.basedeptid
inner join DeptBase c on b.deptid=c.basedeptid
where c.deptid=40
/*
------------------------------------------------------------------------------------------------------------------------------------------------------
福建省福州市鼓楼区
(1 行受影响)
*/
go
--测试数据
SET IDENTITY_INSERT [DeptBase] ON
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (26,NULL,'Dept_26','福建省','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (27,NULL,'Dept_27','浙江省','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (28,NULL,'Dept_28','广东省','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (29,NULL,'Dept_29','广西省','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (30,NULL,'Dept_30','云南省','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (31,26,'Dept_31','福州市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (32,26,'Dept_32','厦门市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (33,26,'Dept_33','漳州市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (34,26,'Dept_34','泉州市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (35,26,'Dept_35','三明市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (36,26,'Dept_36','莆田市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (37,26,'Dept_37','南平市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (38,26,'Dept_38','龙岩市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (39,26,'Dept_39','宁德市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (40,31,'Dept_40','鼓楼区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (41,31,'Dept_41','台江区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (42,31,'Dept_42','仓山区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (43,31,'Dept_43','晋安区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (44,31,'Dept_44','马尾区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (45,31,'Dept_45','福清市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (46,31,'Dept_46','长乐市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (47,31,'Dept_47','闽侯县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (48,31,'Dept_48','平潭县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (49,31,'Dept_49','闽清县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (50,31,'Dept_50','连江县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (51,31,'Dept_51','罗源县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (52,32,'Dept_52','思明区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (53,32,'Dept_53','湖里区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (54,32,'Dept_54','集美区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (55,32,'Dept_55','海沧区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (56,32,'Dept_56','同安区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (57,32,'Dept_57','翔安区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (58,33,'Dept_58','芗城区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (59,33,'Dept_59','龙文区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (60,33,'Dept_60','龙海市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (61,33,'Dept_61','漳浦县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (62,33,'Dept_62','华安县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (63,33,'Dept_63','东山县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (64,33,'Dept_64','长泰县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (65,33,'Dept_65','云霄县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (66,33,'Dept_66','南靖县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (67,33,'Dept_67','平和县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (68,33,'Dept_68','诏安县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (69,34,'Dept_69','洛江区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (70,34,'Dept_70','泉港区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (71,34,'Dept_71','晋江市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (72,34,'Dept_72','石狮市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (73,34,'Dept_73','惠安县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (74,34,'Dept_74','安溪县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (75,34,'Dept_75','德化县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (76,34,'Dept_76','永春县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (77,34,'Dept_77','南安市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (79,35,'Dept_79','三元区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (80,35,'Dept_80','梅列区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (81,35,'Dept_81','永安市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (82,35,'Dept_82','清流县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (83,35,'Dept_83','宁化县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (84,35,'Dept_84','建宁县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (85,35,'Dept_85','泰宁县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (86,35,'Dept_86','明溪县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (87,35,'Dept_87','将乐县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (88,35,'Dept_88','沙县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (89,35,'Dept_89','尤溪县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (90,35,'Dept_90','大田县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (93,36,'Dept_93','城厢区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (94,36,'Dept_94','涵江区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (95,36,'Dept_95','秀屿区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (96,37,'Dept_96','延平区','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (97,37,'Dept_97','邵武市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (99,37,'Dept_99','武夷山','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (100,37,'Dept_100','建瓯市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (101,37,'Dept_101','建阳市','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (102,37,'Dept_102','顺昌县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (103,37,'Dept_103','浦城县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (104,37,'Dept_104','光泽县','可空',0)
INSERT [DeptBase] ([DeptID],[BaseDeptID],[DeptName],[DeptText],[DeptTel],[IsForbid]) VALUES (105,37,'Dept_105','松溪县','可空',0)
SET IDENTITY_INSERT [DeptBase] OFF