有l(wèi)ogs表的結(jié)構(gòu)為uesrid | status | order111 | 5 | 7111 | 0 | 9111 | 1 | 8222 | 3 | 3222 | 1 | 2222 | 2 | 4333 | 3 | 10請問如何先以order desc排序，并通過userid去重，同時(shí)獲取userid，status，order的值。想要的結(jié)果為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這么寫語法就報(bào)錯(cuò)，請問oracle該如何實(shí)現(xiàn)？
3 回答
慕娘9325324
TA貢獻(xiàn)1783條經(jīng)驗(yàn) 獲得超4個(gè)贊
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 關(guān)注
- 1775 瀏覽
添加回答
舉報(bào)
0/150
提交
取消