表text 中 有id,name,time三列id name st
100 a 1
100 b 3
100 c 1
200 a 3
200 b 3
300 a 1
300 b 3
我想选出 不同ID 最后1行 ST列是3的数据上面的表查询出的结果id name st
200 b 3
300 b 3我可能解释的不是很清楚,不明白的再问我,谢谢大侠们了
100 a 1
100 b 3
100 c 1
200 a 3
200 b 3
300 a 1
300 b 3
我想选出 不同ID 最后1行 ST列是3的数据上面的表查询出的结果id name st
200 b 3
300 b 3我可能解释的不是很清楚,不明白的再问我,谢谢大侠们了
2. 当 st 相同的时候,如何处理,例如你的 id = 200 的,你选 b 是随便选的还是什么?
with text as
(
select 100 as id, 'a' as name, 1 as st from dual
union all
select 100 as id, 'b' as name, 3 as st from dual
union all
select 100 as id, 'c' as name, 1 as st from dual
union all
select 200 as id, 'a' as name, 3 as st from dual
union all
select 200 as id, 'b' as name, 3 as st from dual
union all
select 300 as id, 'a' as name, 1 as st from dual
union all
select 300 as id, 'b' as name, 3 as st from dual
)
select id, name, st
from (select id, name, st,
row_number() over(partition by id order by st desc, name desc) as rn
from text)
where rn = 1; ID NAME ST
---------- ---- ----------
100 b 3
200 b 3
300 b 3
select t.* from text t where not exists (select 1 from text where id = t.id and st > t.st) order by t.id