分组后取前 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()

A1：SQL 取出表中数据

A2：按 category_id 分组
A3：在每组中取前四条
A4：将各组数据合并

集算器可以通过 JDBC 与 JAVA 集成，就和使用数据为差不多，详细可参考

【Java 如何调用 SPL 脚本】

【集算器简化 SQL 式计算之各组前 N 行】