主页 / dba / 问题 / 205030
Accepted
VansFannel
Asked: 2018-04-27 02:11:14 +0800 CST

获取表关系之间涉及的所有行

  • 772

我有一个带有下表的 SQL Server 2016：

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
)

Aggregation并AggregationChildren表示表中行之间的关系Code。

聚合将有 1 个或多个子聚合。而且，聚合子项中的代码可以有 0 个或多个聚合子项。

它是这样的：

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

以上都是Code.Serial价值观。

Code.CodeId这些连续出版物的所有值都在和的Aggregation表Code1中。Code2Code3

并且，对于那些连续剧，Code2、Code3、Code4、Code5、Code6、Code7和Code8在AggregationChildren表中。

我需要更新Code.CommissioningFlag所有这些连续剧。

我创建了一个存储过程来递归调用。我作为参数传递Code1，它将对Code2and进行递归调用Code3。

此方法适用于一些代码，但现在，我必须更新 3,000 个代码，它会阻止 SQL Server。

我以为我已经导航抛出“树”并获得一个包含所有代码的临时表，以及所有代码的执行和更新，但我不知道如何在 SQL 中“导航树”。

我怎样才能让所有的代码一次更新所有的代码？

sql-server t-sql

1 个回答

  1. Best Answer

    根据您的解释，我认为您可以使用递归解决方案。我已经建立了一个最小的例子：

    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

    | 代码编号 |
| -----: |
| 1 |

| 编号 |
| -: |
| 1 |

    锚点从 中选择第一行，dbo.Aggregation递归查询行从中匹配 AggregationId 的。dbo.AggregationChildrenAggregationIddbo.Aggregation

    然后使用递归查询返回的 ID 列表简单地更新Code表。

    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 行受影响

    SELECT * FROM dbo.Code;
GO

    代码编号 | 系列 | 聚合级别 ID | 调试标志
-----: | :----- | :---------------- | :----------------
     1 | 代码1 | 1 | 5个                
     2 | 代码2 | 2 | 5个                
     3 | 代码3 | 2 | 5个                
     4 | 代码4 | 3 | 5个                
     5 | 代码5 | 3 | 5个                
     6 | 代码6 | 3 | 5个                
     7 | 代码7 | 3 | 5个                
     8 | 代码8 | 3 | 5个                
     9 | 代码9 | 1 | 3个                
    10 | 代码10 | 2 | 3个                
    11 | 代码11 | 2 | 3个

    db<>fiddle here

    • 1

相关问题