orcale 查询分组后的前n条记录
使用row_number() OVER(PARTITION BY col1,col2 ORDER BY col3 desc)
示例如下:
select * from (SELECT col1,col2,col4,row_number() OVER(PARTITION BY col1,col2 ORDER BY col3 desc) e FROM table1 where col5=‘55’) t where e <= 2
以col1,col2 分组,然后根据col3降序排序 然后再取分组后的每组的前5条记录
col1 col2 col3 e
a1 a2 c2 1
a1 a2 c1 2
a1 b2 c2 1
a1 b2 c1 2
a1 b3 c2 1
a1 b3 c1 2
.................................
a11 b3 c2 1
................................
注:group by order by (1)先group 再order (2) order by中的列必须要在group by中