如何获取所有值都与唯一id相同的行

Question

我有下面提到的表格：

ID       State      City       Pincode      Code      Date
U-1      AAB        CCV        141414       121       2018-04-04 18:08:17
U-1      AAB        CCV        141414       121       2018-04-04 18:08:17
U-2      BTB        ERV        150454       145       2018-05-05 19:11:25
U-2      BTB        ERV        150454       145       2018-05-05 19:11:25
U-3      FFT        ERT        160707       150       2018-05-22 21:37:45
U-4      FFT        RTT        160707       150       2018-05-28 14:23:48

我只想获取在特定唯一ID中所有值都相同的那些行。

输出：

ID       State      City       Pincode      Code      Date
U-1      AAB        CCV        141414       121       2018-04-04 18:08:17
U-1      AAB        CCV        141414       121       2018-04-04 18:08:17
U-2      BTB        ERV        150454       145       2018-05-05 19:11:25
U-2      BTB        ERV        150454       145       2018-05-05 19:11:25

Answer 1

获取重复行并将结果连接到原始表。

select * from table a
join ( select id,state,city,pincode,code,date
       from table 
       group by id,state,city,pincode,code,date 
       having count(*) > 1 ) b
on a.id = b.id
and a.state = b.state
and a.city = b.city
and a.pincode = b.pincode
and a.code = b.code
and a.date=b.date

Answer 2

您可以尝试这样做：

SELECT * FROM table WHERE ID IN (
    SELECT count(*) AS c FROM table 
    WHERE c > 1 
    GROUP BY ID
)

获取具有此ID的记录的计数大于2的所有行(至少具有此id的两行)