Блог

Главная - Вопросы по программированию - Найдите значения 1 столбца в другом и, если они существуют, сохраните значение в 3-м столбце

Найдите значения 1 столбца в другом и, если они существуют, сохраните значение в 3-м столбце

#sql-server #vlookup #hierarchical-data

Вопрос:

Я пытаюсь создать иерархию в одной строке внутри таблицы

У меня есть таблица с 2 основными столбцами, Emp(Сотрудник) и Mgr(Менеджер). Мне нужно выполнить vlookup(эквивалентный в SQL Server) значения mgr и проверить, что в списке emp и, если он существует, мне нужно поместить значение mgr совпадения в MH1. Затем vlookup(эквивалент в SQL Server) MH1 снова в списке emp и, если соответствует, поместите соответствующее значение mgr в MH2 и так далее.. до тех пор, пока не будет совпадений значений mgr. Ниже приведено изображение результата выборки

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

 Emp     Mgr     MH1  MH2    MH3
Mark    Thomas  Bob  Kim    Tim
Robert  Clain   Barry      Murray   
Chris   Crain   Kale       Kelvin   
Andrew              
Thomas  Bob         
Clain   Barry           
Crain   Kale            
Murray  Tom         
Bob Kim         
Kim Tim         
Kale    Kelvin          
Barry   Murray

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

1. всегда ли глубина иерархии не превышает 3, как показано в вашем примере?

2. В соответствии с руководством по вопросам, пожалуйста, покажите, что вы пробовали, и расскажите нам, что вы нашли (на этом сайте или в другом месте) и почему это не соответствует вашим потребностям.

3. Тим Майлотт, глубина иерархии может быть максимальной до 10.

4. Дейл К, я попытался использовать ;С, чтобы создать временную таблицу с emp, mgr, а затем, кроме того, я пытаюсь выполнить левое соединение с временной таблицей и исходной таблицей в поле mgr. Пытаюсь выполнить несколько левых соединений, но не могу достичь иерархии

Ответ №1:

Вы можете сделать это в виде трех отдельных UPDATE утверждений, самостоятельно присоединившись к таблице обратно в Emp 

 UPDATE t1
SET MH1 = t2.Mgr
FROM table t1
JOIN table t2 ON t2.Emp = t1.Mgr;

UPDATE t1
SET MH2 = t2.Mgr
FROM table t1
JOIN table t2 ON t2.Emp = t1.MH1;

UPDATE t1
SET MH3 = t2.Mgr
FROM table t1
JOIN table t2 ON t2.Emp = t1.MH2;

Ответ №2:

Метод 1: Используйте рекурсивный cte. Это быстро, но вам нужна непрерывная иерархическая связь в вашей таблице входных данных, чтобы получить желаемый результат:

 WITH CTE(HierarchyStr, Emp, Mgr) 
AS (
    SELECT CAST(Emp AS nvarchar(1000)), Emp, Mgr 
        FROM T where Mgr IS NULL
    UNION ALL
    SELECT CAST(T.Emp   ', '   CTE.HierarchyStr AS nvarchar(1000)), T.Emp, T.Mgr 
        FROM T JOIN CTE on T.Mgr = CTE.Emp  
)
SELECT 
    HierarchyStr
FROM 
    CTE;

Метод 2: Этот довольно интуитивно понятный TSQL может лучше соответствовать вашим требуемым выводам, но он будет медленным для больших таблиц, используйте его с осторожностью:

 DECLARE @MhColName varchar(10) = 'Mgr';
DECLARE @OldMhColName varchar(10);
DECLARE @I int = 0;
DECLARE @Done bit = 0;
DECLARE @CheckSql nvarchar(1000);

SELECT Emp, Mgr INTO #ResultTable FROM T;

WHILE (@Done = 0)
BEGIN
    SET @OldMhColName = @MhColName;
    SET @I = @I   1;
    SET @MhColName = 'MH'   CAST(@I AS varchar(2));

    EXEC('ALTER TABLE #ResultTable ADD '   @MhColName   ' nvarchar(30)');

    EXEC('UPDATE R1 SET '   @MhColName   ' = R2.Mgr 
            FROM 
                #ResultTable R1, #ResultTable R2
            WHERE R1.'   @OldMhColName   ' = R2.Emp');

    SET @CheckSql = 
         'IF NOT EXISTS(SELECT * FROM #ResultTable WHERE '   @MhColName   ' IS NOT NULL)
          BEGIN
            ALTER TABLE #ResultTable DROP COLUMN '   @MhColName   ';
            SET @Done = 1;
          END';
    EXECUTE sp_executesql @CheckSql, N'@Done bit OUTPUT', @Done = @Done OUTPUT;
END;

SELECT * FROM #ResultTable;

Ввод Образца:

 Emp     Mgr
Mark    Thomas
Thomas  Bob
Bob     Kim
Kim     Tim
Tim     Kelvin
Andrew  NULL
Kelvin  Andrew

Способ 1 Вывод:

 HierarchyStr
Andrew
Kelvin, Andrew
Tim, Kelvin, Andrew
Kim, Tim, Kelvin, Andrew
Bob, Kim, Tim, Kelvin, Andrew
Thomas, Bob, Kim, Tim, Kelvin, Andrew
Mark, Thomas, Bob, Kim, Tim, Kelvin, Andrew

Способ 2 Вывод:

 Emp     Mgr     MH1     MH2     MH3     MH4     MH5
Mark    Thomas  Bob     Kim     Tim     Kelvin  Andrew
Thomas  Bob     Kim     Tim     Kelvin  Andrew  NULL
Bob     Kim     Tim     Kelvin  Andrew  NULL    NULL
Kim     Tim     Kelvin  Andrew  NULL    NULL    NULL
Tim     Kelvin  Andrew  NULL    NULL    NULL    NULL
Andrew  NULL    NULL    NULL    NULL    NULL    NULL
Kelvin  Andrew  NULL    NULL    NULL    NULL    NULL
Метки: , ,