从MySql中查找重复项

Question

假设我有一个如下所示的验证表。

     validation_id    scenario_id
     100              1
     101              1
     102              1
     103              2
     104              2
     100              3
     101              3
     102              3

在上表中，如果我们观察到场景id 1和3具有相同的验证id 100、101和102。

在这种情况下，我可以说scenario_id=1是scenario_id=3的重复。

我可以得到一个MySql查询来找到这样的重复。

提前谢谢。

Answer 1

SELECT validation_ids, GROUP_CONCAT(scenario_id SEPARATOR ', ') AS scenario_ids
FROM (

    SELECT scenario_id, GROUP_CONCAT(validation_id ORDER BY validation_id SEPARATOR ', ') AS validation_ids
    FROM (

        SELECT * FROM (
        SELECT      100 validation_id, 1 scenario_id
        UNION SELECT     101 validation_id, 1 scenario_id
        UNION SELECT     102 validation_id, 1 scenario_id
        UNION SELECT     103 validation_id, 2 scenario_id
        UNION SELECT     104 validation_id, 2 scenario_id
        UNION SELECT     100 validation_id, 3 scenario_id
        UNION SELECT     101 validation_id, 3 scenario_id
        UNION SELECT     102 validation_id, 3 scenario_id) A

        ) B

    GROUP BY scenario_id
) C

GROUP BY validation_ids

Answer 2

您可以使用两个级别的聚合：

select validation_ids, group_concat(scenario_id)
from (select scenario_id,
             group_concat(validation_id order by validation_id) as validation_ids
      from validations v
      group by scenario_id
     ) v
group by validation_ids
having count(*) >= 2;