有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 | 10SELECT * FROM (SELECT * FROM `logs` ORDER BY `order` DESC) AS a GROUP BY a.userid;在mysql下，可以通過以下SQL獲得。但是oracle這么寫語法就報(bào)錯，請問oracle該如何實(shí)現(xiàn)？
2 回答
交互式愛情
TA貢獻(xiàn)1712條經(jīng)驗(yàn) 獲得超3個贊
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
;
- 2 回答
- 0 關(guān)注
- 1110 瀏覽
添加回答
舉報(bào)
0/150
提交
取消