mysql - 将重复的匹配对分组到一个组中 - SQL

Question

第一篇文章在这里!

我想了解的是如何在 SQL Server 中为不同的重复项分配一个整体组 ID？

例如我有下表:

Test | ID | Pair
A      34    1  
A      84    1
A      34    2
A      88    2
A      36    3
A      88    3
A      40    4
A      42    4
B      34    1
B      98    1

我想做的是按照“X 匹配 Y，但 Y 也匹配 Z，因此 X 也匹配 Z”的概念将它们分组到每个测试中？所以我得到类似的东西:

Test | ID | NewGroupID
A      34    1
A      84    1
A      34    1
A      88    1
A      36    1
A      88    1
A      40    2
A      42    2
B      34    1
B      98    1

您注意到在上面的示例中，对于测试 A，34 匹配 84，34 匹配 88，但 88 也匹配 36，因此 ID 34、84、88、36 都是一组。ID 34 也与 ID 98 匹配，但这是针对测试 B 的，因此它位于该测试集的新组中。

我在想我可能需要使用游标来循环？

非常感谢大家的任何意见。谢谢汤姆

Answer 1

这是在 MySQL 5.6.19 中测试的适用于您的测试数据的 SELECT 语句。请注意，您需要在 test, pair, ID 上使用唯一键或主键，以确保以正确的顺序处理数据。它不是最优雅的代码，如果您提供的测试数据不具有代表性，则可能会变薄或需要调整。

SET @pg=1;
SET @t='A';
SET @pairs="1";
SELECT  pg.test AS Test,
    p.ID, 
    pg.pair_group AS NewGroupID
FROM
    pairs p
LEFT
JOIN    (
    SELECT  DISTINCT pair,
        pair_group,
        test
    FROM    (
        SELECT  test,
            pair,
            IF(FIND_IN_SET(pair, @pairs), @pg, IF(pair_sets.test = @t, @pg:=@pg+1, @pg:=1)) pair_group,
            @pairs:=pairs,
            @t:=test
        FROM    (
            SELECT  t.test,
                p.pair,
                GROUP_CONCAT(p.pair) pairs
            FROM    (
                SELECT  DISTINCT(test) from pairs ORDER BY test) AS t
            LEFT
            JOIN    pairs p ON p.test = t.test
            GROUP
            BY  t.test,
                p.id
            ORDER
            BY  t.test,
                p.pair,
                pairs
            ) AS pair_sets
        ) AS pair_groups
    ) AS pg ON pg.pair = p.pair AND pg.test = p.test;