选择包含唯一值(非冗余)的行

Question

给定此表：

日期2022-02-262022-02-26[/tr>

id	名称
21	B1
21	B
01	C1	2022-02-26
04	T1	2022-02-16
04	T	2022-02-16

DB

CREATE TABLE test (
id INT,
name CHAR,
date DATE
);
INSERT INTO test VALUES (1, 'A', '2022-02-22');
INSERT INTO test VALUES (1, 'B', '2022-02-21');
INSERT INTO test VALUES (2, 'C', '2022-02-20');
INSERT INTO test VALUES (3, 'D', '2022-02-19');

查询

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

输出

id  name date
2   C    2022-02-20
3   D    2022-02-19

db fiddle

with cte as 
(
select id, count(Name) as cnt
from TableName
group by id
)
select cte.id, tableName.Name
from cte 
inner join TableName on
cte.id = tableName.id
where cte.cnt = 1 

select id,name from test
where ID NOT IN ( select id from test group by id having count(*)> 1)

select * from test where id not in
(select distinct id from 
(select *,row_number () 
over (partition by id order by Name asc) rn 
from test
) b
where rn>1)

假设表名为Test。下面的查询会起作用。

Select *
From Test
Group by Id
Having count(*) = 1

最有效的方法可能是使用窗口函数来检查是否有另一行

SELECT *
FROM (
SELECT *,
LEAD(id) OVER (PARTITION BY id ORDER BY Name) rn 
FROM YourTable t
) t
WHERE t.nxt IS NULL;