如下表:
ID C2 C3
1 2 60
1 3 70
1 4 80
1 5 77
2 2 76
2 3 66
2 6 60
3 1 90
4 1 68
4 2 72求一个SQL语句,可得取每个ID取C3列最大的两条记录,结果如下
ID C2 C3
1 4 80
1 5 77
2 2 76
2 3 66
3 1 90
4 2 72
4 1 68
ID C2 C3
1 2 60
1 3 70
1 4 80
1 5 77
2 2 76
2 3 66
2 6 60
3 1 90
4 1 68
4 2 72求一个SQL语句,可得取每个ID取C3列最大的两条记录,结果如下
ID C2 C3
1 4 80
1 5 77
2 2 76
2 3 66
3 1 90
4 2 72
4 1 68
select ID,C2,C3 FROM
(select row_number() over(partition by ID order by c3 desc) rn,A.* from A)
where rn<=2
where rn<=2