SQL -为具有重复值的列集选择所有主键

Question

把表当作-

mysql> SELECT * FROM DB;

+------+--------+------+------------+---------+
| UDID | BENEID | DEPT | SCHEME     | NAME    |
+------+--------+------+------------+---------+
|    1 |     55 | TCS  | RC         | SHELLY  |
|    2 |     95 | TCS  | RC         | BOB     |
|    3 |     75 | TCS  | RC         | ULRICH  |
|    4 |     55 | TCS  | RC         | SHELLY  |
|    5 |     85 | TCS  | FS         | SHELLY  |
|    6 |     65 | DSSP | ABC        | BOB     |
|    7 |     65 | DSSP | ABC        | BOB     |
|    8 |     75 | DSSP | ABC        | ULRICH  |
+------+--------+------+------------+---------+

列beneid、set 和udid有重复的值，具有不同的主键udid。那是,

mysql> SELECT DEPT, SCHEME, BENEID, COUNT(*) AS CN FROM DB GROUP BY 1, 2, 3 HAVING CN >1;
+------+--------+--------+----+
| DEPT | SCHEME | BENEID | CN |
+------+--------+--------+----+
| DSSP | ABC    |     65 |  2 |
| TCS  | RC     |     55 |  2 |
+------+--------+--------+----+

我们有两个重复列的部门，方案，贝纳德，但与不同的主键(udid)。如何列出上述结果的udids？

预期表结构-

+------+--------+--------+----+-----+
| DEPT | SCHEME | BENEID | CN | UDID|
+------+--------+--------+----+-----+
| DSSP | ABC    |     65 |  2 | 6   |
| DSSP | ABC    |     65 |  2 | 7   |
| TCS  | RC     |     55 |  2 | 1   |
| TCS  | RC     |     55 |  2 | 4   |
+------+--------+--------+----+-----+

Answer 1

您需要“返回”到原始表，方法是对已被发现为重复的行进行筛选。

示例：

SELECT DB.DEPT, DB.SCHEME, DB.BENEID, DUPS.CN, DB.UID
FROM DB
INNER JOIN
  (
    SELECT DEPT, SCHEME, BENEID, COUNT(*) AS CN
    FROM DB GROUP BY 1, 2, 3 HAVING CN >1
  ) AS DUPS ON DB.DEPT = DUPS.DEPT
     and DB.SCHEME = DUPS.SCHEME
     and DB.BENEID = DUPS.BENEID

Answer 2

您可以使用作为子查询列出的聚合查询，并使用exists调用包装它：

SELECT *
FROM   db a
WHERE  EXISTS (SELECT   b.dept, b.scheme, b.beneid
               FROM     db b
               WHERE    a.dept = b.dept AND 
                        a.scheme = b.scheme AND
                        a.beneid = b.beneid
               GROUP BY b.dept, b.scheme, b.beneid
               HAVING   COUNT(*) > 1)