oracle rownumber over partition by,oracle 排序 row_number() over(partition by 排序字段)

业务描述：按t.truckId,t.riskCode 分组，每个分组里有分数，取分组中分数最大的那条记录。

如：A1 B1   5  6

A1  B1   5  3

A1  B2   2  5

A1  B2   2  1

已经按 A,B分组了 但是 我需要第一条和第三条数据，就可以用这个row_number() over(partition by  排序字段)降序

然后取 rowseq = 1 的数据就可以拿到了

SELECT

gg.sumScore     AS sumScore,

gg.numberRisk   AS numberRisk,

gg.happenTime   AS happenTime

FROM (

SELECT

g.sumScore      AS sumScore,

g.numberRisk    AS numberRisk,

g.happenTime    AS happenTime ,

(row_number() over(partition by g.truckId  order by g.sumScore desc) ) as rowseq --分数最大的排在前面

FROM(

SELECT

sum(score)                    as sumScore  ,--分数

count(t.riskCode)          as numberRisk ,

max(t.happenTime)      as  happenTime,

t.truckId                      as  truckId

FROM

(

SELECT

t1.truck_id  truckId,

to_char(t1.happen_time,'yyyy-mm-dd hh24:mi:ss')   AS  happenTime,

t2.score         As  score,

t2.code          As  riskCode

FROM  b_risk_event t1

INNER JOIN d_dictionary_info t2 ON t1.risk_type = t2.code

WHERE t1.happen_time >  SYSDATE-30/(24*60)

AND t1.del_flag='0'

AND t2.father_type in('1','2','3','5','8','17','18','0')

order by t1.happen_time DESC

) t

GROUP BY t.truckId,t.riskCode

ORDER  BY sumScore DESC,happenTime DESC

)g

) gg

WHERE   gg.rowseq =1