时间戳

首页 > 解决方案 > 为在 SQL 中连接超过 2 列的行组分配唯一 ID

sql - 为在 SQL 中连接超过 2 列的行组分配唯一 ID

问题描述

我的情况如下：

我有一个包含 2 个 ID 列的表，我想为通过任一列链接的行分配一个唯一 ID。这是我表中 6 行的示例。所有这些行都需要获得相同的唯一 ID。

排 ID1 ID2
1 一个 1
2 一个 2
3 乙 2
4 乙 3
5 C 3
6 C 4

第 1 行和第 2 行需要获得相同的唯一 ID，因为它们具有相同的 ID1。

第 3 行也需要得到它，因为它的 ID2 与第 2 行的 ID2 匹配。

第 4 行也需要得到它，因为它的 ID1 与第 3 行的 ID1 匹配。

第 5 行也需要得到它，因为它的 ID2 与第 4 行的 ID2 匹配。

第 6 行也需要得到它，因为它的 ID1 与第 5 行的 ID1 匹配。

基本上这两列形成一个链，我想为该链分配一个 ID。在 SQL 中是否有一些相当有效的方法来做到这一点？

标签： sqlsql-server

解决方案

好的。这是一个非常笨拙的解决方案（我可能应该交出我的 SQL Server 徽章，甚至建议它），但我认为它会让你越过界限。我只是希望您不要在非常大的数据集上运行它。

首先，我创建了一些虚拟临时表来模拟您的数据（加上一些额外的行）：

DROP TABLE IF EXISTS #Tbl1 ;
CREATE TABLE #Tbl1
(
    [id] TINYINT NOT NULL
    , [ID1] CHAR(1) NOT NULL
    , [ID2] TINYINT NOT NULL
) ;
INSERT
    INTO    #Tbl1 ( [id], [ID1], [ID2] )
    VALUES  ( 1, 'A', 1 ), ( 2, 'A', 2 ), ( 3, 'B', 2 )
            , ( 4, 'B', 3 ), ( 5, 'C', 3 ), ( 6, 'C', 4 )
            , ( 7, 'D', 5 ), ( 8, 'D', 5 ), ( 9, 'E', 6 ) ;

然后，使用 CTE 和 LAG 函数，我确定了哪些行会看到唯一标识符的增量，并将所有这些转储到临时表中：

DROP TABLE IF EXISTS #Tbl2 ;
WITH cte_Lags AS
(
    SELECT          [id]
                    , [ID1]
                    , LAG ( [ID1], 1, '' )
                        OVER ( ORDER BY [ID1] ASC, [ID2] ASC ) AS [ID1_lag]
                    , [ID2]
                    , LAG ( [ID2], 1, 0 )
                        OVER ( ORDER BY [ID1] ASC, [ID2] ASC ) AS [ID2_lag]
    FROM            #Tbl1
)
SELECT          [id] AS [row]
                , [ID1]
                , [ID2]
                , CASE
                    WHEN    [ID1] = [ID1_lag]
                    OR      [ID2] = [ID2_lag]
                        THEN    0
                    ELSE        1
                    END AS [incr_id]
    INTO        #Tbl2
FROM            cte_Lags ;

然后，我将您的唯一 ID 列添加到临时表中：

ALTER TABLE #Tbl2 ADD [unique_id] TINYINT NULL ;

现在这才是真正混乱的地方！

我创建了一个迭代循环，循环遍历临时表的每一行并unique_id使用适当的数字更新列，仅当行被标记为递增时才递增：

DECLARE @RowNum AS TINYINT ;
DECLARE @i AS TINYINT = 0 ;

WHILE   ( ( SELECT COUNT(*) FROM #Tbl2 WHERE [unique_id] IS NULL ) > 0 )
BEGIN
    SELECT      TOP(1) @RowNum = [row]
    FROM        #Tbl2
    WHERE       [unique_id] IS NULL
    ORDER BY    [ID1] ASC, [ID2] ASC, [row] ASC ;

    IF          ( ( SELECT [incr_id] FROM #Tbl2 WHERE [row] = @RowNum ) = 1 )
        SET         @i += 1 ;

    UPDATE      #Tbl2
    SET         [unique_id] = @i
    WHERE       [row] = @RowNum ;
END

SELECT      [row]
            , [ID1]
            , [ID2]
            , [unique_id]
FROM        #Tbl2
ORDER BY    [ID1] ASC, [ID2] ASC ;

现在这一切都假设数据不会在表格的下方重复出现——例如 ('A', 1) 不会再次出现在第 50 行。如果确实如此，这一切都需要重新考虑。

我真的希望比我更聪明的人可以在一个简单的递归 CTE 或一个时髦的分组函数中为你做这件事。但在那之前，这会让老板高兴。

推荐阅读