使用 SQLite 选择重复列组合的记录 [英] Select records where a combinations of columns are repeated using SQLite

问题描述

我有一个包含以下列的表格:

I have a table with the following columns:

a|b|c|d|e

并且我想选择所有记录，以便重复列的子集组合(例如 a、b、c).示例:

and I want to select all the records such that a combination of a subset of the columns is repeated (say, a, b, c). Example:

a|b|c|d|e
1|2|3|1|2
6|7|8|9|1
1|2|3|4|5
1|2|2|5|4

应该导致:

1|2|3|1|2
1|2|3|4|5

推荐答案

这样做:

SELECT a,b,c,d,e
  FROM t t1
 WHERE EXISTS ( SELECT NULL
                  FROM t t2
                 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c
                   AND t1.rowid <> t2.rowid
              )

第二种方式(SQLite不支持(x,y) in (select two columns)):

A second way (SQLite does not support (x,y) in (select two columns)):

SELECT a,b,c,d,e
  FROM t
 WHERE quote(a)||','||quote(b)||','||quote(c)
    IN ( SELECT quote(a)||','||quote(b)||','||quote(c)
           FROM t
          GROUP BY a,b,c
         HAVING COUNT(*) > 1
       )

第三种方式:

SELECT a,b,c,d,e
  FROM ( SELECT a,b,c
           FROM t
          GROUP BY a,b,c
         HAVING COUNT(*) > 1
       )
NATURAL JOIN t

最后一个有一个优点，你还可以添加聚合行(就像你在其他 RDBMS 中使用分析函数一样:)

This last one has an advantage, you can also add aggregate rows (like if you used analytic functions in other RDBMS:)

sqlite> .headers on
sqlite> .mode column
sqlite> .width 3 3 3 3 3 20
sqlite> SELECT a,b,c,d,e,"Rows in this group"
   ...>   FROM ( SELECT a,b,c
   ...>               , COUNT(*) AS "Rows in this group"
   ...>            FROM t
   ...>           GROUP BY a,b,c
   ...>          HAVING COUNT(*) > 1
   ...>        )
   ...> NATURAL JOIN t;
a    b    c    d    e    Rows in this group
---  ---  ---  ---  ---  --------------------
1    2    3    1    2    2
1    2    3    4    5    2

在 Oracle 中与此类似:

which is similar to this in Oracle:

   WITH x AS
      (SELECT a,b,c,d,e,COUNT(*) OVER (PARTITION BY a,b,c) "Rows in this group"
         FROM t
      )
   SELECT * FROM x WHERE "Rows in this group" >= 2

这篇关于使用 SQLite 选择重复列组合的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！