在两列中查找重复记录的唯一记录 [英] Find unique record for duplicate records over two columns

问题描述

这是我的数据库数据集:

This is my database dataset :

表

ID             Name

XXX-23305      Edward, Stue^Jenna
XXX-23305      Edward, Stue^Jenna
XXX-23306      Cole, Slaw^Bali
XXX-23306      Cole, Slaw^Bali
XXX-23306      Cole, Slaw^Bali
XXX-23310      Zerg, War^Finja
XXX-23310      Road^Sieglinde
XXX-23319      Gras, Dr.Carl^Yolo
XXX-23319      Gras, Dr.Carl^Yolo

如您所见，同一ID和名称组合可能会有多个条目. 但是，对于ID XXX-23310，有两个不同的名称可用.

As you can see there might be multiple entries for the same ID and Name combination. However in case of ID XXX-23310 there are two different names available.

现在我要显示的是确切的数据集(我有一个大约31k条目的数据库，其中这些条目的数量未知).

Now what I want is displaying that exact dataset ( I have a database of like 31k entries with an unnknown amount of those entries ).

有没有办法做到这一点?我用谷歌搜索了一段时间，也浏览了这里的其他帖子，但找不到任何东西.

Is there a way to achieve that? I googled for some time and also had a look at other posts here but was unable to find anything.

查询的所需输出:

ID             Name

XXX-23310      Zerg, War^Finja
XXX-23310      Road^Sieglinde

不确定是否有人阅读过此内容，但感谢您的解决方案.我想添加另一个条件.仅在ID包含多个条目的地方输出数据.例如.如果我的样本数据集仅包含XXX-23310的一项，则查询结果将不会显示任何内容.

not sure if anyone reads this but thanks for the solutions. I want to add another condition. Only output the data where there are more than one entry for the ID. E.g. if my sample dataset would just contain one entry for XXX-23310 then the query would print nothing as a result.

推荐答案

如果您还想排除给定ID的任何单独记录，则:

If you also want to exclude any lone records for a given ID:

create table #data
(
    id varchar(10),
    [name] varchar(50)
)

insert into #data
values
    ('XXX-23305','Edward, Stue^Jenna'),
    ('XXX-23306','Cole, Slaw^Bali'),
    ('XXX-23306','Cole, Slaw^Bali'),
    ('XXX-23306','Cole, Slaw^Bali'),
    ('XXX-23310','Zerg, War^Finja'),
    ('XXX-23310','Road^Sieglinde'),
    ('XXX-23319','Gras, Dr.Carl^Yolo'),
    ('XXX-23319','Gras, Dr.Carl^Yolo');

with d as
(   
    select distinct
        id,
        [name]
    from #data
)
select *
from d
where d.id in
(   
    select d.id
    from d
    group by d.id
    having count(*) > 1
)

drop table #data

返回XXX-23310的两个记录，但不返回XXX-23305的其他记录:

Returns the two record for XXX-23310, but not XXX-23305 as other answers would do:

/-----------------------------\
| id        | name            |
|-----------|-----------------|
| XXX-23310 | Road^Sieglinde  |
| XXX-23310 | Zerg, War^Finja |
\-----------------------------/

这篇关于在两列中查找重复记录的唯一记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！