时间戳

首页 > 解决方案 > 如何根据评估（SQL Server）有条件地连接两个表以获取结果？

sql - 如何根据评估（SQL Server）有条件地连接两个表以获取结果？

问题描述

我的数据库中有两个表：

办公室

id       Department         Section       PositionID         
-------------------------------------------------------
1    |   DataCenter   |    Developer  |       10     
2    |   DataCenter   |    DBA        |       11     
3    |   DataCenter   |    SA         |       12     
4    |   DataCenter   |    HelpDesk   |       13     
5    |   DataCenter   |    DepHead    |       14
6    |   Produce      |    Stocker    |       19
7    |   Produce      |    Farmer     |       20

职位

id        PosName         OfficeID       Level         
-------------------------------------------------------
10    |  Senior Dev   |       1     |     1     
11    |  Senior DBA   |       2     |     1 
12    |  Senior SA    |       3     |     1 
13    |  Help Desk    |       4     |     2 
14    |  Supervisor   |       1     |     0
15    |  Junior Dev   |       1     |     2
16    |  Junior Dev   |       3     |     2
17    |  Junior DBA   |       2     |     2
18    |  Junior DBA   |       2     |     2
19    |  Junior DBA   |       2     |     2
20    |  Junior SA    |       1     |     2

如何根据部门和部门从职位中选择所有条目，但要高于其级别？我猜我需要加入某种经过评估的条件才能做到这一点。

例如，高级开发人员职位位于 1 级。因此，我希望将 2 级的两个初级开发人员职位与其分组。同样，三个 2 级初级 DBA 职位将与高级 DBA 职位相关联。最棘手的部分是获取数据中心中与主管（处于 0 级）相关联的所有职位。

我的最终目标是在网页中使用此查询，在该网页中，分配给某个职位的用户只能看到在他们手下及其部门/部门工作的用户。

因此，最终查询结果可能如下所示：

Department      Section        ManagingPosition         Position        PositionID
-----------------------------------------------------------------------------------
DataCenter   | Developer  |      Senior Dev       |   Junior Dev   |       15    
DataCenter   | Developer  |      Senior Dev       |   Junior Dev   |       16    
DataCenter   | DBA        |      Senior DBA       |   Junior DBA   |       17    
DataCenter   | DBA        |      Senior DBA       |   Junior DBA   |       18   
DataCenter   | DBA        |      Senior DBA       |   Junior DBA   |       19    
DataCenter   | SA         |      Senior SA        |   Junior SA    |       20   
DataCenter   | DepHead    |      Supervisor       |   Junior Dev   |       15
DataCenter   | DepHead    |      Supervisor       |   Junior Dev   |       16
DataCenter   | DepHead    |      Supervisor       |   Junior DBA   |       17
DataCenter   | DepHead    |      Supervisor       |   Junior DBA   |       18
DataCenter   | DepHead    |      Supervisor       |   Junior DBA   |       19
DataCenter   | DepHead    |      Supervisor       |   Junior SA    |       20
DataCenter   | DepHead    |      Supervisor       |   Help Desk    |       13

到目前为止，我有

SELECT
    O.Department,
    O.Section,
    O.Position AS ManagingPosition,
    P.PosName AS Position,
    P.PositionID
FROM
    Offices O
    INNER JOIN Positions P on O.PositionID = P.id

不过，我知道这是不正确的。查询这些表以获得我想要的结果的最佳方法是什么？我也可以修改这些表（添加列）以获得结果。我很感激任何帮助！

编辑：

为澄清起见，职位表中的级别以级别 0 为最高，级别 2 为最低。所以 0 级应该能够看到更大的东西（即 1 级和 2 级），而 1 级应该只能看到 2 级。

标签： sqlsql-servertsqljoin

解决方案

首先，您需要添加一列来定义每个职位的父职位。

在此处输入图像描述

然后一个选项是使用 CTE 递归（https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/）

WITH PositionsCTE ( PositionID, ManagingPosition, Position, OfficeId, [Level])
AS
(   
    --starts with the very first level 
    SELECT
        id as PositionID, cast('' as varchar(50)) as ManagingPosition,
        PosName, OfficeId, [Level]
    FROM Positions 
    WHERE ParentId IS NULL -- (supervisor in this case)
    UNION ALL
    -- and then recursivity 
    SELECT
        p.id as PositionID, Pcte.Position as ManagingPosition,
        p.PosName, p.OfficeId, p.[Level]
    FROM Positions AS p
    INNER JOIN PositionsCTE Pcte ON p.ParentId = Pcte.PositionID
)
Select 
    o.Department,
    o.Section, 
    p.ManagingPosition,
    p.Position,
    p.PositionID
from Offices o
inner join PositionsCTE p on o.id = p.OfficeId;

推荐阅读