CODE NUM RATE
A 1 1.2
A 2 0.9
A 3 1.1
B 9 0.4
B 7 0.7
B 8 0.9
C 4 0.0
C 5 0.6
查询结果
CODE NUM RATE
A 3 1.1
B 9 0.4
C 5 0.6
说明:查询结果为当CODE相同时NUM最大的记录.用一条SQL语句
不知道说清楚没有,在线等,多谢各位关注
A 1 1.2
A 2 0.9
A 3 1.1
B 9 0.4
B 7 0.7
B 8 0.9
C 4 0.0
C 5 0.6
查询结果
CODE NUM RATE
A 3 1.1
B 9 0.4
C 5 0.6
说明:查询结果为当CODE相同时NUM最大的记录.用一条SQL语句
不知道说清楚没有,在线等,多谢各位关注
FROM (SELECT CODE,
NUM,
RATE,
ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY NUM DESC, RATE DESC) RN
FROM YOURTABLE)
WHERE RN = 1;
先查询按code 分组,max num,然后再用原表关联查询,方法2 给每条纪录用row_number() over(partition by code order by num desc) 生成一个编号
然后再用外层过滤编号>1的
如果需要有并列的,可以用dense_rank()
from (select CODE,
NUM,
RATE,
row_number() over(partition by CODE order by NUM desc) RN
from yourtable) t
where t.RN = 1
SQL> select * from AAAA;CODE NUM RATE
---------- ---------- ----------
A 1 1.2
A 2 .9
A 3 1.1
B 9 .4
B 7 .7
B 8 .9
C 4 0
C 5 .6已选择8行。
SQL> select aaaa.* from aaaa,
2 (select code,max(num) mx from aaaa group by code) tt
3 where aaaa.code=tt.code and aaaa.num=tt.mx;CODE NUM RATE
---------- ---------- ----------
A 3 1.1
B 9 .4
C 5 .6SQL>