显示所有重复的行 [英] Show all duplicated rows
问题描述
假设我有以下 sql 表
suppose I have following sql table
objid firstname lastname active
1 test test 0
2 test test 1
3 test1 test1 1
4 test2 test2 0
5 test2 test2 0
6 test3 test3 1
现在，我感兴趣的结果如下:
Now, the result I am interested in is as follows:
objid firstname lastname active
1 test test 0
2 test test 1
4 test2 test2 0
5 test2 test2 0
我怎样才能做到这一点?我尝试了以下查询，
How can I achieve this? I have tried the following query,
select firstname,lastname from table
group by firstname,lastname
having count(*) > 1
但是这个查询给出了像
firstname lastname
test test
test2 test2
推荐答案
您已找到重复的记录，但您有兴趣获取附加到它们的所有信息.您需要将重复项join 到主表中以获取该信息.
You've found your duplicated records but you're interested in getting all the information attached to them. You need to join your duplicates to your main table to get that information.
select *
from my_table a
join ( select firstname, lastname
from my_table
group by firstname, lastname
having count(*) > 1 ) b
on a.firstname = b.firstname
and a.lastname = b.lastname
这与 inner join 相同，这意味着对于子查询中的每条记录，找到重复记录后，您会从主表中找到所有具有相同 firstseen 和 lastseen 组合的内容.
This is the same as an inner join and means that for every record in your sub-query, that found the duplicate records you find everything from your main table that has the same firstseen and lastseen combination.
您也可以使用 in, 尽管您应该测试差异:
You can also do this with in, though you should test the difference:
select *
from my_table a
where ( firstname, lastname ) in
( select firstname, lastname
from my_table
group by firstname, lastname
having count(*) > 1 )
