首頁(yè) 猿問(wèn) oracle 指定字段排序并去重？

oracle 指定字段排序并去重？

Oracle

三國(guó)紛爭(zhēng) 2018-08-14 11:09:17

有l(wèi)ogs表的結(jié)構(gòu)為uesrid | status | order111 | 5 | 7111 | 0 | 9111 | 1 | 8222 | 3 | 3222 | 1 | 2222 | 2 | 4333 | 3 | 10請(qǐng)問(wèn)如何先以order desc排序，并通過(guò)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下，可以通過(guò)以下SQL獲得。但是oracle這么寫(xiě)語(yǔ)法就報(bào)錯(cuò)，請(qǐng)問(wèn)oracle該如何實(shí)現(xiàn)？

查看完整描述

2 回答

交互式愛(ài)情

TA貢獻(xiàn)1712條經(jīng)驗(yàn) 獲得超3個(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

;

反對(duì) 回復(fù) 2018-08-26

函數(shù)式編程

TA貢獻(xiàn)1807條經(jīng)驗(yàn) 獲得超9個(gè)贊

用開(kāi)窗函數(shù)就可以呀：

反對(duì) 回復(fù) 2018-08-26

2 回答
0 關(guān)注
1138 瀏覽

關(guān)注

添加回答

舉報(bào)

0/150

提交

取消

使用 Ctrl+D 可將網(wǎng)站添加到書(shū)簽

微信客服

購(gòu)課補(bǔ)貼
聯(lián)系客服咨詢優(yōu)惠詳情

幫助反饋 APP下載

慕課網(wǎng)APP
您的移動(dòng)學(xué)習(xí)伙伴

公眾號(hào)

掃描二維碼
關(guān)注慕課網(wǎng)微信公眾號(hào)

第七色在线视频,2021少妇久久久久久久久久,亚洲欧洲精品成人久久av18,亚洲国产精品特色大片观看完整版,孙宇晨将参加特朗普的晚宴

熱搜

最近搜索清空

oracle 指定字段排序并去重？

oracle 指定字段排序并去重？

2 回答

添加回答

oracle 指定字段排序并去重？

oracle 指定字段排序并去重？