在sqlite中选择唯一记录 [英] Select unique records in sqlite

问题描述

考虑一张桌子

结果(id，键，值)，其中key是主键.

results (id, key, value) where key is the Primary key.

样本数据:

id | key      | value
-----------------------
abc| source-1 | 20
abc| source-2 | 30
abc| source-3 | 2 
abc| source-4 | 10
def| source-5 | 1 
ghi| source-6 | 25
jkl| source-5 | 13

我只想返回那些具有给定ID的单个条目的记录.所以输出应该是

I would like to return only those records which have a single entry for a given id. So output should be

id | key      | value
------------------------
def| source-5 | 1
ghi| source-6 | 25
jkl| source-5 | 13

请告知.

推荐答案

一种方法是使用GROUP BY和HAVING生成具有所需id的派生表，然后将其联接:

One way is to use GROUP BY and HAVING to produce a derived table with the desired ids and then JOIN to it:

select results.*
from results
join (
    select id
    from results
    group by id
    having count(*) = 1
) as dt on results.id = dt.id

如果您不喜欢派生表，也可以使用IN:

You could also use an IN if you don't like derived tables:

select *
from results
where id in (
    select id
    from results
    group by id
    having count(*) = 1
)

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