11.11.1 序号函数
1.ROW_NUMBER()函数
ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
例如,查询t_goods数据表中每个商品分类下价格最高的3种商品信息。
mysql> SELECT * FROM
-> (
-> SELECT
-> ROW_NUMBER() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS row_num,
-> id, t_category_id, t_category, t_name, t_price, t_stock
-> FROM t_goods) t
-> WHERE row_num <= 3;
+---------+----+---------------+---------------------+-----------------+---------+---------+
| row_num | id | t_category_id | t_category | t_name | t_price | t_stock |
+---------+----+---------------+