我有一張這樣的桌子：
treatment | patient_id
3 | 1
3 | 1
3 | 1
2 | 1
2 | 1
1 | 1
2 | 2
2 | 2
1 | 2
我只需要這樣的行max(treatment)：
treatment | patient_id
3 | 1
3 | 1
3 | 1
2 | 2
2 | 2
1patient_id就是max(treatment)3 patient_id2max(treatment)就是 2
uj5u.com熱心網友回復：
例如，您可以使用最大值加入聚合表：
select t.*
from tmp t
inner join (
select max(a) max_a, b
from tmp
group by b
) it on t.a = it.max_a;
這是分貝小提琴。
uj5u.com熱心網友回復：
嘗試這個 ：
WITH list AS
( SELECT patient_id, max(treatment) AS treatment_max
FROM your_table
GROUP BY patient_id
)
SELECT *
FROM your_table AS t
INNER JOIN list AS l
ON t.patient_id = l.patient_id
AND t.treatment = l.treatment_max
uj5u.com熱心網友回復：
您可以使用rank：
with u as
(select *, rank() over(partition by patient_id order by treatment desc) r
from table_name)
select treatment, patient_id
from u
where r = 1;
小提琴
uj5u.com熱心網友回復：
使用相關子查詢
select t1.* from table_name t1
where t1.treatment=( select max(treatment) from table_name t2 where t1.patient_id=t2.patient_id
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/436623.html
標籤：sql PostgreSQL 每组最大 n