Início / dba / Perguntas / 205030
Accepted
VansFannel
Asked: 2018-04-27 02:11:14 +0800 CST

Obtenha todas as linhas envolvidas entre as relações de tabelas

  • 772

Tenho um SQL Server 2016 com as seguintes tabelas:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            VARCHAR(20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

Aggregatione AggregationChildrenrepresentam as relações entre as linhas na tabela Code.

Uma agregação terá 1 ou mais filhos de agregação. E também, um código em filhos de agregação pode ter 0 ou mais filhos de agregação.

É algo assim:

Code1
|___________________
|                   |
Code2               Code3
|________           |________________
|        |          |        |       |
Code4    Code5      Code6    Code7   Code8

Todos os itens acima são Code.Serialvalores.

Todos os Code.CodeIdvalores para esses seriais estão na Aggregationtabela para Code1, Code2e Code3.

E, para esses seriados, Code2, Code3, Code4, Code5, Code6, Code7, e Code8estão na AggregationChildrentabela.

Eu preciso atualizar Code.CommissioningFlagpara todos esses seriados.

Eu criei um procedimento armazenado para chamado recursivamente. Eu passo Code1como argumento e ele fará uma chamada recursiva para Code2e Code3.

Esse método funciona bem com alguns códigos, mas agora preciso atualizar 3.000 códigos e ele bloqueia o SQL Server.

Eu pensei em navegar, lançar a 'árvore' e obter uma tabela temporária com todos os códigos, e fazer e atualizar para todos eles, mas não sei como 'navegar na árvore' no SQL.

Como posso obter todos os códigos para atualizar todos de uma vez?

sql-server t-sql

1 respostas

  1. Best Answer

    De acordo com sua explicação, acho que você poderia usar uma solução recursiva. Eu configurei um exemplo mínimo:

    INSERT INTO dbo.Code VALUES
(1,  'Code1',  1, 1),
(2,  'Code2',  2, 1),
(3,  'Code3',  2, 1),
(4,  'Code4',  3, 1),
(5,  'Code5',  3, 1),
(6,  'Code6',  3, 1),
(7,  'Code7',  3, 1),
(8,  'Code8',  3, 1),
(9,  'Code9',  1, 3),
(10, 'Code10', 2, 3),
(11, 'Code11', 2, 3);
GO

    INSERT INTO dbo.Aggregation VALUES (1),(2),(3),(9);
GO

    INSERT INTO dbo.AggregationChildren VALUES
(2,  1, 1),
(3,  1, 2),
(4,  2, 1),
(5,  2, 2),
(6,  3, 1),
(7,  3, 2),
(8,  3, 3),
(10, 9, 1),
(11, 9, 2);
GO

    DECLARE @Serial VARCHAR(20) = 'Code1';

SELECT CodeId FROM dbo.Code WHERE Serial = @Serial;

SELECT    AggregationId Id
FROM      dbo.Aggregation
WHERE     AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial);
GO

    | CódigoId |
| -----: |
| 1 |

| ID |
| -: |
| 1 |

    A âncora seleciona a primeira linha de dbo.Aggregation, e as linhas de consulta recursiva de dbo.AggregationChildrenonde AggregationIdcorrespondem AggregationId de dbo.Aggregation.

    Em seguida, basta atualizar Codea tabela usando a lista de IDs retornada pela consulta recursiva.

    DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
UPDATE c
SET    CommissioningFlag = 5
FROM   dbo.Code c
JOIN   rc
ON     c.CodeId = rc.Id;
GO

    8 linhas afetadas

    SELECT * FROM dbo.Code;
GO

    CódigoId | Série | AggregationLevelId | Bandeira de comissionamento
-----: | :----- | :----------------- | :----------------
     1 | Código1 | 1 | 5                
     2 | Código2 | 2 | 5                
     3 | Código3 | 2 | 5                
     4 | Código4 | 3 | 5                
     5 | Código5 | 3 | 5                
     6 | Código 6 | 3 | 5                
     7 | Código 7 | 3 | 5                
     8 | Código8 | 3 | 5                
     9 | Código9 | 1 | 3                
    10 | Código10 | 2 | 3                
    11 | Código11 | 2 | 3

    db<>fiddle here

    • 1

relate perguntas