我的 postgres sql 查詢有問題。我想找到具有特定條件的重復項。
| ID | address_id | 狀態 |
|---|---|---|
| 12 | 1 | 一種 |
| 94 | 1 | 一種 |
| 991 | 1 | 一種 |
| 992 | 2 | 一種 |
| 993 | 2 | 一種 |
條件：我想根據 address_id 查找重復項，它們應該具有狀態“A”
所以我寫了查詢：
select count(*), g.address_id
from tableName g
where g.state = 'A'
group by g.address_id
having count(*)
> 1
當我想獲取所有值時，我只是將查詢擴展到：
SELECT w.* from tableName w
(select count(*), g.address_id
from tableName g
where g.state = 'A'
group by g.address_id
having count(*)
> 1) x on w.address_id = x.address_id
在輸出中，我得到所有重復項的串列。但我想獲得具有最高 id 的重復項。
根據我的表，我想獲得輸出：
| ID | address_id | 狀態 |
|---|---|---|
| 991 | 1 | 一種 |
| 993 | 2 | 一種 |
uj5u.com熱心網友回復：
另一種方式 ：
select tn.id,tn.address_id,tn.state
from tableName tn
inner join (select max(id) as id ,count(address_id) as nr_count
from tableName
where state='A'
group by address_id
) as t1 on tn.id=t1.id
where t1.nr_count >1;
演示
您可以使用視窗功能：
select max(id) as id ,address_id,state
from (
SELECT id, address_id,state
, count(*) OVER ( PARTITION BY address_id ) AS cnt
FROM tableName
where state='A'
) as t1
where cnt>1
group by address_id,state;
演示
uj5u.com熱心網友回復：
您可以使用exists檢查重復分組address_id并加入或使用in：
select *
from tableName
where id in (
select max(id)
from tableName tn
where exists(select * from tableName itn where tn.address_id = itn.address_id
and tn.id <> itn.id)
group by address_id
);
要么
select otn.*
from tableName otn
join (
select max(id) id
from tableName tn
where exists(select * from tableName itn where tn.address_id = itn.address_id
and tn.id <> itn.id)
group by address_id
) groupped on otn.id = groupped.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/443640.html
標籤：sql PostgreSQL 加入 重复