分组后取前 N 条
【问题】
suppose i have one table say ‘abc’ and it has got two column product_id and category_id.There can be more than one product_id for one category_id. Now what i need is how can we select first four rows from all the available category_id in a that table…i was able to do this in mysql server database using "row_number over partition by (….." but unfortunately this not supported in mysql…Can any one help me out,how to write this query.
【回答】
正如你所说，各组前 N 列的问题应该用窗口函数来解决，但 MySQL 没有窗口函数，解决起来要麻烦许多。如果数据量不是太大时，可以试试 SPL 了，做这种麻烦上很简单，代码如下：
A |
|
1 |
=db.query(“select * from abc”) |
2 |
=A1.group(category_id) |
3 |
=A2.(~.top(4)) |
4 |
=A3.union() |
A2：按 category_id 分组
A3：在每组中取前四条
A4：将各组数据合并
集算器可以通过 JDBC 与 JAVA 集成，就和使用数据为差不多，详细可参考
