MySQL 8.0窗口函数

1. MySQL 8.0窗口函数 ROW_NUMBER() OVER()函数的使用

• order_tab.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for order_tab
-- ----------------------------
DROP TABLE IF EXISTS `order_tab`;
CREATE TABLE `order_tab`  (
  `order_id` int(0) NOT NULL,
  `user_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `amount` int(0) NULL DEFAULT NULL,
  `create_date` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order_tab
-- ----------------------------
INSERT INTO `order_tab` VALUES (1, '001', 100, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (2, '001', 300, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (3, '001', 500, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (4, '001', 800, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (5, '001', 900, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (6, '002', 500, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (7, '002', 600, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (8, '002', 300, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (9, '002', 800, '2022-05-01 15:40:27');
INSERT INTO `order_tab` VALUES (10, '002', 800, '2022-05-01 15:40:27');

SET FOREIGN_KEY_CHECKS = 1;

SELECT
	*
FROM
	(
	SELECT
		ROW_NUMBER() OVER ( PARTITION BY user_no
	ORDER BY
		amount DESC ) AS row_num,
		order_id,
		user_no,
		amount,
		create_date
	FROM
		order_tab ) t;