如何在SQL中找到谁是部门主管

我想创建一个存储过程来查找谁是部门的主管。所有经理都包含在前缀为"的部门中_头部"；。

我有两个具有这种结构的表，并且使用SQL Server 2014。

SQL Server表：

个人

PersonID | DepartID | PersonName
---------+----------+----------------------
138    |   110    | RootManager
248    |    90    | Manager1
63    |    73    | User1
218    |    73    | User2
216    |    96    | Manager2
161    |    93    | Manager3
222    |    93    | Manager4
118    |    23    | User3
160    |    23    | User4
259    |    86    | User5

更新：

PersonID - PK
Person.DepartID = Depart.DepartID - FK

离开

DepartID | ParentID | DepartName
---------+----------+--------------
77    | NULL     | NULL
100    | 77       | _Head of Root
73    | 77       | Dep1
90    | 73       | _Head of Dep1
98    | 77       | Dep2
96    | 98       | _Head of Dep2
23    | 98       | SubDep2
93    | 23       | _Head of SubDep2
86    | 77       | Dep3

更新：

DepartID - PK
Depart.ParentID = Depart.DepartID - FK
Depart.DepartID = Person.DepartID - FK

&quot_某事物的首脑；可以包含没有以前部门名称的随机字符

例如："_SubDep2负责人；可以是"_部门负责人XXX/YYY"；

树视图：

ROOT [DepartID = 77, ParentID = NULL]
|- _Head of Root [DepartID = 110, ParentID = 77]
|- RootManager [PersonID = 138, DepartId = 110]
|- Dep1 [DepartID = 73, ParentID = 77]
|- _Head of Dep1 [DepartID = 90, ParentID = 73]
|- Manager1 [PersonID = 248, DepartId = 90]
|- User1 [PersonID = 63, DepartId = 73]
|- User2 [PersonID = 218, DepartId = 73]
|- Dep2 [DepartID = 98, ParentID = 77]
|- _Head of Dep2 [DepartID = 96, ParentID = 98]
|- Manager2 [PersonID = 216, DepartId = 96]
|- SubDep2 [DepartID = 23, ParentID = 98]
|- _Head of SubDep2 [DepartID = 93, ParentID = 23]
|- Manager3 [PersonID = 161, DepartId = 93]
|- Manager4 [PersonID = 222, DepartId = 93]
|- User3 [PersonID = 118, DepartId = 23]
|- User4 [PersonID = 160, DepartId = 23]
|- Dep3 [DepartID = 86, ParentID = 77]
|- User5 [PersonID = 259, DepartId = 86]

期望结果：

User1，User2=Manager1[PerseID=248]
User3，User4=Manager3，Manager4[PerseID=161222]
User5=根管理器[个人ID=138]
Manager1，Manager2=RootManager[PersonID=138]
Manager3=Manager2[PerseID=216]

我尝试过：

使用INNER JOIN的SQL查询，但它只适用于一个级别。

SQL与CTE，我组合了Department表，但我不能与Person表组合。

有人能帮我谈谈这个话题吗？

非常感谢的支持时间

我建议重新审视并规范您的数据库设计。在停机期间，如果这是一个正在使用的数据库，则使用视图作为抽象来查询数据可以提供无缝的更改，直到您重新访问模型为止。一旦模型更新完成，只需更新视图，使用该视图的所有应用程序都不需要更改。

对于您当前的设计，以下查询将有助于根据识别部门负责人的模式来确定经理：

方法1[推荐]

CREATE TABLE Person (
PersonID INTEGER,
DepartID INTEGER,
PersonName VARCHAR(11)
);
INSERT INTO Person
(PersonID, DepartID, PersonName)
VALUES
('138', '110', 'RootManager'),
('248', '90', 'Manager1'),
('63', '73', 'User1'),
('218', '73', 'User2'),
('216', '96', 'Manager2'),
('161', '93', 'Manager3'),
('222', '93', 'Manager4'),
('118', '23', 'User3'),
('160', '23', 'User4'),
('259', '86', 'User5');


CREATE TABLE Depart (
DepartID INTEGER,
ParentID VARCHAR(4),
DepartName VARCHAR(16)
);
INSERT INTO Depart
(DepartID, ParentID, DepartName)
VALUES
('77', NULL, NULL),
('100', '77', '_Head of Root'),
('73', '77', 'Dep1'),
('90', '73', '_Head of Dep1'),
('98', '77', 'Dep2'),
('96', '98', '_Head of Dep2'),
('23', '98', 'SubDep2'),
('93', '23', '_Head of SubDep2'),
('86', '77', 'Dep3');
GO

19行影响

DROP PROCEDURE IF EXISTS sp_GetManager
GO

✓

CREATE PROC sp_GetManager (@Name VARCHAR(20))
AS
BEGIN
DECLARE @ManagerResult TABLE(PersonId INTEGER, PersonName VARCHAR(20));
INSERT INTO @ManagerResult
SELECT 
p.PersonId, 
p.PersonName
FROM 
Person p 
WHERE p.DepartId in ( 
SELECT d.DepartId from Depart d 
WHERE d.DepartName IN (
SELECT  CONCAT('_Head of ',d1.DepartName) FROM
Depart d1
INNER JOIN Person p1 on p1.DepartId = d1.DepartId
WHERE p1.PersonName = @Name
)


)

IF EXISTS(
Select PersonId from @ManagerResult
)
SELECT * from @ManagerResult
ELSE
SELECT p.PersonId, p.PersonName FROM Person p where p.PersonName='RootManager'
END
GO

✓

exec sp_GetManager 'User1'
GO

PersonId|PersonName-------：|：---------248|Manager1

<blockquote\

exec sp_GetManager 'User2'
GO

PersonId|PersonName-------：|：---------248|Manager1

<blockquote\

exec sp_GetManager 'Manager3'
GO

PersonId|PersonName-------：|：----------138 | RootManager

<blockquote\

exec sp_GetManager 'User5'
GO

PersonId|PersonName-------：|：----------138 | RootManager

<blockquote\

exec sp_GetManager 'User3'
GO

PersonId|PersonName-------：|：---------161 |经理3222|Manager4

<blockquote\

exec sp_GetManager 'User4'
GO

PersonId|PersonName-------：|：---------161 |经理3222|Manager4

<blockquote\

exec sp_GetManager 'Manager1'
GO

PersonId|PersonName-------：|：----------138 | RootManager

<blockquote\

exec sp_GetManager 'Manager2'
GO

PersonId|PersonName-------：|：----------138|RootManager

db<gt；小提琴这里

方法2

这似乎更乏味，但如果你打算用最初的递归方法遍历树，那么这种尝试可能会引导你基于外键遍历层次结构

select @@version;
GO

|(无列名)||：---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------||Microsoft SQL Server 2019(RTM-CU6)(KB4563110)-15.0.4053.23(X64)<br>2020年7月25日11:26:55<br>版权所有(C)2019 Microsoft Corporation<br>Windows Server 2019 Standard 10.0<X64>(内部版本17763:)(Hypervisor)<br>

CREATE TABLE Person (
PersonID INTEGER,
DepartID INTEGER,
PersonName VARCHAR(11)
);
INSERT INTO Person
(PersonID, DepartID, PersonName)
VALUES
('138', '110', 'RootManager'),
('248', '90', 'Manager1'),
('63', '73', 'User1'),
('218', '73', 'User2'),
('216', '96', 'Manager2'),
('161', '93', 'Manager3'),
('222', '93', 'Manager4'),
('118', '23', 'User3'),
('160', '23', 'User4'),
('259', '86', 'User5');


CREATE TABLE Depart (
DepartID INTEGER,
ParentID VARCHAR(4),
DepartName VARCHAR(16)
);
INSERT INTO Depart
(DepartID, ParentID, DepartName)
VALUES
('77', NULL, NULL),
('100', '77', '_Head of Root'),
('73', '77', 'Dep1'),
('90', '73', '_Head of Dep1'),
('98', '77', 'Dep2'),
('96', '98', '_Head of Dep2'),
('23', '98', 'SubDep2'),
('93', '23', '_Head of SubDep2'),
('86', '77', 'Dep3');
GO

19行影响

DROP PROCEDURE IF EXISTS sp_GetManager
GO

✓blockquote\CREATE PROC sp_GetManager(@Name VARCHAR(20))
AS
BEGIN
DECLARE @ManagerResult TABLE(PersonId INTEGER, PersonName VARCHAR(20), ManagementLevel INTEGER);
DECLARE @PersonId INTEGER, @PersonDepartmentId INTEGER;
SELECT @PersonId = p.PersonId , @PersonDepartmentId = p.DepartId  FROM Person p 
WHERE p.PersonName  = @Name;

WITH 
head_of_department AS  (
SELECT d.DepartId, d.ParentId, d.DepartName, 0 as Level
FROM Depart d WHERE d.DepartId = @PersonDepartmentId
UNION ALL
SELECT d.DepartId, d.ParentId, d.DepartName, Level+1 as Level
FROM Depart d 
INNER JOIN head_of_department hod on d.DepartId = hod.ParentId
WHERE d.ParentId is NOT NULL  
),
including_sub_department AS (
SELECT DISTINCT * FROM (
SELECT * from head_of_department
UNION ALL
SELECT d.DepartId, d.ParentId, d.DepartName, hod.Level
FROM Depart d 
INNER JOIN head_of_department hod on hod.DepartId = d.ParentId

) t WHERE t.DepartName LIKE '_Head of%'
)
INSERT INTO @ManagerResult
select   p.PersonId, p.PersonName ,d.Level
from including_sub_department d
INNER JOIN Person p on d.DepartId = p.DepartId
WHERE 
d.DepartId <> @PersonDepartmentId;
DECLARE @MinManagementLevel INTEGER;
IF EXISTS(
Select PersonId from @ManagerResult
)
BEGIN
SELECT @MinManagementLevel=MIN(ManagementLevel) FROM @ManagerResult;
SELECT PersonId, PersonName from @ManagerResult WHERE ManagementLevel = @MinManagementLevel;
END
ELSE
SELECT p.PersonId, p.PersonName FROM Person p where p.PersonName='RootManager'

END
GO
✓

exec sp_GetManager 'User1'
GO

PersonId|PersonName-------：|：---------248|Manager1

<blockquote\

exec sp_GetManager 'User2'
GO

PersonId|PersonName-------：|：---------248|Manager1

<blockquote\

exec sp_GetManager 'Manager3'
GO

PersonId|PersonName-------：|：---------216|Manager2

<blockquote\

exec sp_GetManager 'User5'
GO

PersonId|PersonName-------：|：----------138 | RootManager

<blockquote\

exec sp_GetManager 'User3'
GO

PersonId|PersonName-------：|：---------161 |经理3222|Manager4

exec sp_GetManager 'User4'
GO

PersonId|PersonName-------：|：---------161 |经理3222|Manager4

exec sp_GetManager 'Manager1'
GO

PersonId|PersonName-------：|：----------138 | RootManager

<blockquote\

exec sp_GetManager 'Manager2'
GO

PersonId|PersonName-------：|：----------138|RootManager

db<gt；小提琴这里

方法1[推荐]

方法2

相关内容

最新更新

热门标签：