实验结果如下:
1000392 1317150
1000392 1275963
1000392 1246108
1000392 1213591
1000392 1182874
1000444 1415392
1000444 1415384
1000444 1389073
1000444 1387134
1000444 1188199
1000469 1361608
1000469 1321450
1000469 1273338
1000469 1184630
1000469 1184629
数据表,设为表A,第一列是l1,第二列是l2
select l1, l2, row_number
from
(
select a.l1 l1, a.l2 l2, row_number() over (partition by l1 order by l2 desc) row_number
from a
)
where row_number <= 5
如此,可以取出以l1为主排序,l2倒序为次排序的每个组的前5条记录~~
红、蓝部分即关键