mysql分组内排序

最新推荐文章于 2024-04-24 18:22:58 发布

西张明-辉

最新推荐文章于 2024-04-24 18:22:58 发布

阅读量133

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/shenmohui/article/details/130932010

版权

创建表

CREATE TABLE `person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `department` int NOT NULL COMMENT '科室',
  `price` decimal(10,2) NOT NULL COMMENT '价格',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

以department为分组,以price为分组内的排序

	select id,
       department,
       name,
       price
from (select row_number() over (partition by department order by price asc ) as KeyId,
              id,
       department,
       name,
       price
      from person) a
where KeyId = 1