如何只获取表中的重复记录? [英] How to get only repeated records in a table?

问题描述

我不知道要问这个问题有多好，所以我的搜索变得绝望了.

I couldn't figure out how better to ask this question, so my searches became desperate.

我有一个包含三列(Column1，Column2，Column3)的表.有很多记录，其中包含不同和相同的值.我只想获取与column1，column2和column3值完全相同的记录.如何在常规SQL中以最快的查询获取它们?尤其是HSQLDB?

I have a table with three columns(Column1, Column2, Column3). There are lots of records consisted of different and same values in it. I want to get only the exactly same records as column1, column2 and column3 values. How can I get them in the fastest query in general SQL? And especially HSQLDB?

此外，如果我的表有4列(+ Column4)，但我仍然需要column1，column2和column3值的相同记录.查询必须更改还是相同?

Besides, if my table has 4 columns (+ Column4) but still I need same records of column1,column2 and column3 values. Query must change or will be same?

示例；

-------------------------------
| Column1 | Column2 | Column3 |
|    1    |    2    |   3     |  <-- A
|    2    |    2    |   30    |  <-- B
|    3    |    3    |   10    |
|    4    |    12   |   3     |  <-- C
|    1    |    3    |   3     |
|    1    |    4    |   3     |
|    1    |    5    |   3     |
|    4    |    12   |   3     |  <-- C
|    2    |    2    |   30    |  <-- B
|    1    |    2    |   3     |  <-- A
|    4    |    12   |   3     |  <-- C
-------------------------------

所以我需要选择所有 A，B和C记录.结果应该是；

So I need to select all A,B and C records. The result should be;

-------------------------------
| Column1 | Column2 | Column3 |
|    1    |    2    |   3     |  <-- A
|    2    |    2    |   30    |  <-- B
|    4    |    12   |   3     |  <-- C
|    4    |    12   |   3     |  <-- C
|    2    |    2    |   30    |  <-- B
|    1    |    2    |   3     |  <-- A
|    4    |    12   |   3     |  <-- C
-------------------------------

-结果省略了完全不同的记录-

-Result omitted completely different records-

推荐答案

通过与HAVING子句组合进行分组来获取它们.这在HSQLDB中也应该起作用. HAVING子句从行不包含多个行的结果中删除所有数据.当您有一个包含4列的表时，必须采用该语句.

Get them by grouping in combination with a HAVING clause. This should also work in HSQLDB. The HAVING clause removes all data from result a rows isn't contained multiple. When you have a table with 4 columns you have to adopt the statement.

SELECT Column1,Column2,Column3 FROM Table GROUP BY Column1,Column2,Column3 HAVING COUNT(*)>1

编辑:已获取所有行

SELECT x.* FROM Table x INNER JOIN (
    SELECT Column1,Column2,Column3 FROM Table GROUP BY Column1,Column2,Column3 HAVING COUNT(*)>1
) y ON x.Column1=y.Column1 AND x.Column2=y.Column2 AND x.Column3=y.Column3

这篇关于如何只获取表中的重复记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！