鑒于此表：
| ID | 姓名 | 日期 |
|---|---|---|
| 01 | 乙 | 2022-02-22 |
| 01 | C | 2022-02-20 |
| 01 | ? | 2022-02-21 |
| 04 | 噸 | 2022-02-16 |
| 05 | 一種 | 2022-02-15 |
| 05 | 小號 | 2022-02-09 |
| 06 | D | 2022-02-02 |
我想選擇 Id 不重復的行，例如：
| ID | 姓名 | 日期 |
|---|---|---|
| 04 | 噸 | 2022-02-16 |
| 06 | D | 2022-02-02 |
請問如何使用 SQL 查詢來做到這一點。
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
select id,name from test
where ID NOT IN ( select id from test group by id having count(*)> 1)
uj5u.com熱心網友回復：
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)
uj5u.com熱心網友回復：
D B
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小提琴
uj5u.com熱心網友回復：
假設表名是Test. 下面的查詢會起作用。
Select *
From Test
Group by Id
Having count(*) = 1
uj5u.com熱心網友回復：
最有效的方法可能是使用視窗函式來檢查是否還有另一行
SELECT *
FROM (
SELECT *,
LEAD(id) OVER (PARTITION BY id ORDER BY Name) rn
FROM YourTable t
) t
WHERE t.nxt IS NULL;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/432421.html
上一篇：有沒有辦法計算列中值的數量？