有logs表的結構為uesrid | status | order111 | 5 | 7111 | 0 | 9111 | 1 | 8222 | 3 | 3222 | 1 | 2222 | 2 | 4333 | 3 | 10請問如何先以order desc排序，并通過userid去重，同時獲取userid，status，order的值。想要的結果為uesrid | status | order111 | 0 | 9222 | 2 | 4333 | 3 | 10在mysql下，可以通過以下SQL獲得。SELECT * FROM (SELECT * FROM `logs` ORDER BY `order` DESC) AS a GROUP BY a.userid;但是oracle這么寫語法就報錯，請問oracle該如何實現？
3 回答
慕娘9325324
TA貢獻1783條經驗 獲得超4個贊
WITH temp as
(
select '111' as userid,'5' as status,'7' as ord from dual
union all
select '111' as userid,'0' as status,'9' as ord from dual
union all
select '111' as userid,'1' as status,'8' as ord from dual
union all
select '222' as userid,'3' as status,'3' as ord from dual
union all
select '222' as userid,'1' as status,'2' as ord from dual
union all
select '222' as userid,'2' as status,'4' as ord from dual
union all
select '333' as userid,'3' as status,'10' as ord from dual
)
select userid,status,ord from (
select userid,status,ord,row_number() over(partition by userid order by ord desc) rn
from temp
)
where rn=1
;
- 3 回答
- 0 關注
- 1780 瀏覽
添加回答
舉報
0/150
提交
取消