表结构如下：
id name classs num regNo
1 张三 一班 3.5 12
2 张三 一班 2.5 13
3 张三 二班 4.5 15
4 李四 二班 6.5 16
最后结果：
id name classs num regNo
1 张三 一班 6 12
2 张三 二班 4.5 15
3 李四 二班 6.5 16
请问用sql如何实现呢？
select id,name,sum(num) as num,min(regNo) as regNo group by id,name
select rownum as id,t.* from (
select name,
class,
sum(num) as num,
min(regno) as regno
from test1
group by name,class
order by class desc,name desc) t
SELECT * FROM (
SELECT
name,
class,
regno,
SUM(num) OVER (PARTITION BY name,class ORDER BY regno DESC) AS num,
ROW_NUMBER() OVER (PARTITION BY name,class ORDER BY regno ) AS regno
FROM table) tmp
WHERE regno = 1