Блог

Главная - Вопросы по программированию - Как я могу условно объединить две таблицы, чтобы получить результаты на основе оценки (SQL Server)?

Как я могу условно объединить две таблицы, чтобы получить результаты на основе оценки (SQL Server)?

#sql #sql-server #tsql #join

Вопрос:

У меня есть две таблицы в моей базе данных:

Офисы

 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-го уровня будут связаны с должностью старшего администратора базы данных. И самое сложное было бы получить ВСЕ должности в центре обработки данных, связанные с Руководителем (который находится на уровне 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.

Комментарии:

1. Вредные привычки : Использование псевдонимов таблиц, таких как (a, b, c) или (t1, t2, t3) . «А» не для офицеров, и «Б» не для должностей. «О» и «П» были бы гораздо лучшим выбором.

2. при таком дизайне неясно , какая должность является более высоким уровнем другой должности, поэтому вам нужна новая колонка, например «managerpositinId», чтобы назначить менеджера на каждую должность, иначе узнать это невозможно

3. У вас должен быть еще один столбец в таблице позиций. что-то вроде родительской позиции, чтобы знать, кто находится на той или иной должности.

4. @Larnu, ты прав. Это определенно то, с чем я должен работать лучше — спасибо, что держишь меня в узде! Я пошел дальше и обновил свой пост.

5. @eshirvana, приносим извинения за отсутствие ясности. Чем меньше число, тем выше уровень позиции. Я также отредактировал свой пост, чтобы улучшить ясность. Я также рассмотрю идею столбца managerpositionId. Спасибо!

Ответ №1:

Сначала вам нужно добавить столбец, чтобы определить родительскую позицию для каждой позиции.

введите описание изображения здесь

И тогда одним из вариантов является рекурсия с 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;
Метки: ,