主页 / dba / 问题 / 281995
Accepted
Nir
Asked: 2020-12-23 08:06:59 +0800 CST

查找具有重复版本的组

  • 772

我想在表中查找具有重复组版本的所有组。一个组可以有多个组版本。每个组版本可以有多个成员。组“版本”由grpid和定义changeDate。如果一个组版本中的所有成员（和）与同一组中的另一个组版本匹配userid，则该组认为重复。pcthobby

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=84eb81a1a71dcee9ad3d0bd91f56120a

表groups：

ID grpid 改变日期 用户身份 百分比 爱好 团体版*
1 1 2020-01-01 1 1 1 1
2 1 2020-01-02 1 1 2 2
3 1 2020-01-03 1 1 1 3
4 2 2020-01-01 1 0.5 1 4
5 2 2020-01-01 2 0.5 2 4
6 2 2020-01-02 1 0.5 1 5
7 2 2020-01-02 2 0.5 3 5
8 3 2020-01-01 1 0.5 1 6
9 3 2020-01-01 2 0.5 2 6
10 3 2020-01-02 1 0.4 1 7
11 3 2020-01-02 2 0.6 2 7
12 4 2020-01-01 1 0.6 1 8
13 4 2020-01-01 2 0.4 2 8
14 4 2020-01-02 1 0.6 1 9
15 4 2020-01-02 2 0.4 2 9
16 5 2020-01-01 1 0.2 2 10
17 5 2020-01-01 2 0.5 1 10
18 5 2020-01-01 3 0.3 2 10
19 6 2020-01-01 1 0.3 2 11
20 6 2020-01-01 2 0.5 1 11
21 6 2020-01-01 3 0.2 2 11
22 6 2020-02-01 1 0.2 2 12
23 6 2020-02-01 2 0.5 1 12
24 6 2020-02-01 3 0.3 2 12
25 6 2020-03-01 1 0.3 2 13
26 6 2020-03-01 2 0.3 1 13
27 6 2020-03-01 3 0.4 2 13
28 7 2020-01-01 1 0.3 2 14
29 7 2020-01-01 2 0.5 1 14
30 7 2020-01-01 3 0.2 2 14
31 7 2020-02-01 1 0.3 2 15
32 7 2020-02-01 2 0.5 1 15
33 7 2020-02-01 3 0.2 2 15
34 7 2020-03-01 1 0.3 2 16
35 7 2020-03-01 2 0.3 1 16
36 7 2020-03-01 3 0.4 2 16
37 8 2020-02-01 1 0.3 1 17
38 8 2020-03-01 1 0.3 1 18
39 8 2020-03-01 3 0.4 2 18

*唯一组版本号仅用于可视化。

结果应该是：

grpid
1
4
7

解释：

  • grpid 1 - 有 3 个组版本（1 个成员） - 1 和 3 个重复，因为 userid、pct 和 hobby 相等
  • grpid 2 - 有 2 个组版本（2 个成员） - 不重复，因为 5 和 7 之间的爱好不相等
  • grpid 3 - 有 2 个组版本（2 个成员） - 不重复，因为 pct 在所有成员中都不同
  • grpid 4 - 有 2 个组版本（2 个成员） - 所有成员都是重复的，因为 userid、pct 和 userid 相等
  • grpid 5 - 只有一组 3 名成员 - 不重复
  • grpid 6 - 有 3 个组版本（3 个成员） - 不重复 - 版本之间组中每个成员的 pct 更改
  • grpid 7 - 有 3 个组版本（3 个成员） - 重复，因为 userid、pct 和 userid 在 28-30 和 31-33 之间相等
  • grpid 8 - 有 2 个组版本，一个有一个成员，一个有 2 个成员 - 不重复，因为该组中有另一个成员

我正在使用 MySQL 5.7。

mysql select

3 个回答

  1. Best Answer

    希望这应该提供一个答案：

    这实现了“通用”关系划分：

    SELECT DISTINCT grpid
FROM groups AS g
GROUP BY grpid, changeDate
HAVING NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g.grpid
        AND gi.changeDate = g.changeDate
        AND NOT EXISTS
            ( SELECT 1 
               FROM groups AS gk
               WHERE gk.grpid = gi.grpid
                 AND gk.changeDate <> gi.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    ) ;

    结果：

    grid
   1
   4
   7

    这实现了精确的关系除法，这导致了更复杂的代码。您的情况下，分组/版本由两列确定，(grpid, changeDate)这使它看起来更加复杂。

    在dbffdle.uk中测试

    查询一：

    SELECT DISTINCT g1.grpid
FROM 
  ( SELECT grpid, changeDate
    FROM groups AS g
    GROUP BY grpid, changeDate
  ) AS g1
  JOIN
  ( SELECT grpid, changeDate
    FROM groups AS g
    GROUP BY grpid, changeDate
  ) AS g2
  ON  g1.grpid = g2.grpid
  AND g1.changeDate < g2.changeDate
WHERE NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g1.grpid
        AND gi.changeDate = g1.changeDate
        AND NOT EXISTS
            ( SELECT 1 
               FROM groups AS gk
               WHERE gk.grpid = g2.grpid
                 AND gk.changeDate = g2.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    )
   AND NOT EXISTS
    ( SELECT 1
      FROM groups AS gi
      WHERE gi.grpid = g2.grpid
        AND gi.changeDate = g2.changeDate
        AND NOT EXISTS
            ( SELECT 1 
               FROM groups AS gk
               WHERE gk.grpid = g1.grpid
                 AND gk.changeDate = g1.changeDate
                 AND gk.userid = gi.userid
                 AND gk.pct = gi.pct
                 AND gk.hobby = gi.hobby
            )
    )
 ;

    使用 MySQLGROUP_CONCAT函数的查询 2：

    SELECT DISTINCT grpid
FROM
  ( SELECT grpid, changeDate, 
           GROUP_CONCAT( CONCAT_WS('-', userid, hobby, pct)
                         ORDER BY userid, hobby, pct
                         SEPARATOR '  '
                       ) AS groupdata 
    FROM groups AS gr
    GROUP BY grpid, changeDate
  ) AS g
GROUP BY grpid, groupdata
HAVING COUNT(*) > 1 ;
    • 3

  2. 您可以为此使用内部联接

    仅供参考 mysql 8 由于不喜欢组作为表名，并且必须放在反引号中

    并且行 id = 2 和 id = 4 也是“相同的，并且属于您的规则

    create table groups ( id int,  grpid int , changeDate date,  userid int,  pct double ,  hobby int );

    insert into groups(id , grpid , changeDate  ,userid  ,pct  ,hobby ) values
(1  , 1      ,'2020-01-01', 1   ,    1   , 1  ),
(2  , 1      ,'2020-01-02', 1   ,    1   , 2  ),
(3  , 1      ,'2020-01-03', 1   ,    1   , 1  ),
(4  , 2      ,'2020-01-01', 1   ,    0.5 , 1  ),
(5  , 2      ,'2020-01-01', 1   ,    0.5 , 2  ),
(6  , 2      ,'2020-01-02', 1   ,    0.5 , 1  ),
(7  , 2      ,'2020-01-02', 1   ,    0.5 , 3  ),
(8  , 3      ,'2020-01-01', 1   ,    0.5 , 1  ),
(9  , 3      ,'2020-01-01', 1   ,    0.5 , 2  ),
(10 , 3      ,'2020-01-02', 1   ,    0.4 , 1  ),
(11 , 3      ,'2020-01-02', 1   ,    0.6 , 2  ),
(12 , 4      ,'2020-01-01', 1   ,    0.6 , 1  ),
(13 , 4      ,'2020-01-01', 1   ,    0.4 , 2  ),
(14 , 4      ,'2020-01-02', 1   ,    0.6 , 1  ),
(15 , 4      ,'2020-01-02', 1   ,    0.4 , 2  );

    SELECT DISTINCT g1.grpid 
FROM `groups` g1 INNER JOIN  `groups` g2
ON g1.grpid = g2.grpid 
AND g1.hobby = g2.hobby 
AND g1.pct = g2.pct 
AND g1.userid = g2.userid
WHERE g1.id <> g2.id

    | grpid |
| ----: |
| 1 |
| 2 |
| 4 |

    db<>在这里摆弄

    • 2 
  3. SELECT DISTINCT grpid
FROM TableWithGroups
GROUP BY grpid, userid, pct, hobby
HAVING COUNT(*) > 1

    这使用GROUP BY 和 HAVING子句分别将您指定的字段上的值分组以将重复项压缩为一行并过滤每个分组中压缩的行数大于 1（换句话说，存在重复项） .

    • 0

相关问题