按要求写查询语句

MySQL脚本

-- ----------------------------
-- Table structure for contract
-- ----------------------------
DROP TABLE IF EXISTS `contract`;
CREATE TABLE `contract`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `contract_code` varchar(50)  NOT NULL COMMENT '合同编号',
  `contract_name` varchar(50)  NOT NULL COMMENT '合同名称',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8  COMMENT = '合同' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of contract
-- ----------------------------
INSERT INTO `contract` VALUES (1, 'HT001', 'HT001-1', '2024-05-15 00:00:00');
INSERT INTO `contract` VALUES (2, 'HT002', 'HT002-1', '2024-05-15 00:00:00');
INSERT INTO `contract` VALUES (3, 'HT003', 'HT003-1', '2024-05-15 00:00:00');
INSERT INTO `contract` VALUES (4, 'HT001', 'HT001-2', '2024-05-16 08:55:49');
INSERT INTO `contract` VALUES (5, 'HT002', 'HT002-2', '2024-05-16 08:55:49');
INSERT INTO `contract` VALUES (6, 'HT003', 'HT003-2', '2024-05-16 08:55:49');
INSERT INTO `contract` VALUES (7, 'HT004', 'HT004-1', '2024-05-15 08:55:49');

mysql> SELECT * FROM contract;
+----+---------------+---------------+---------------------+
| id | contract_code | contract_name | create_time         |
+----+---------------+---------------+---------------------+
|  1 | HT001         | HT001-1       | 2024-05-15 00:00:00 |
|  2 | HT002         | HT002-1       | 2024-05-15 00:00:00 |
|  3 | HT003         | HT003-1       | 2024-05-15 00:00:00 |
|  4 | HT001         | HT001-2       | 2024-05-16 08:55:49 |
|  5 | HT002         | HT002-2       | 2024-05-16 08:55:49 |
|  6 | HT003         | HT003-2       | 2024-05-16 08:55:49 |
|  7 | HT004         | HT004-1       | 2024-05-15 08:55:49 |
+----+---------------+---------------+---------------------+
7 rows in set (0.00 sec)

mysql>

要求：

1. 合同编号有重复的
2. 根据创建时间（create_time）倒序 获取最新的合同名称、合同编号

预期结果：

mysql>
+---------------+---------------+
| contract_code | contract_name |
+---------------+---------------+
| HT001         | HT001-2       |
| HT002         | HT002-2       |
| HT003         | HT003-2       |
+---------------+---------------+
3 rows in set (0.00 sec)

mysql>

思路分析

1. CTE（公共表表达式）：使用 WITH RankedContracts AS (...) 创建一个公共表表达式，命名为 ranked_contracts
2. ROW_NUMBER()：在 SELECT 中使用 ROW_NUMBER() OVER (PARTITION BY contract_code ORDER BY create_time DESC) 为每个合同编号分配一个序号。 PARTITION BY contract_code 表示对每个合同编号进行分组
3. 主查询：在主查询中，从 ranked_contracts 中选择合同编号、合同名称和创建时间，只保留 rn > 1 的记录，即合同编号有重复的的记录
4. ORDER BY：最后，按创建时间倒序排列结果

参考答案

WITH ranked_contracts AS (
    SELECT 
        contract_code,
        contract_name,
        create_time,
        ROW_NUMBER() OVER (PARTITION BY contract_code ORDER BY create_time ASC) AS rn
    FROM 
        contract
)
SELECT contract_code, contract_name 
FROM ranked_contracts 
WHERE rn > 1
ORDER BY create_time DESC;

• 运行结果如下

mysql> WITH ranked_contracts AS (
    ->     SELECT 
    ->         contract_code,
    ->         contract_name,
    ->         create_time,
    ->         ROW_NUMBER() OVER (PARTITION BY contract_code ORDER BY create_time ASC) AS rn
    ->     FROM 
    ->         contract
    -> )
    -> SELECT contract_code, contract_name 
    -> FROM ranked_contracts 
    -> WHERE rn > 1
    -> ORDER BY create_time DESC;
+---------------+---------------+
| contract_code | contract_name |
+---------------+---------------+
| HT001         | HT001-2       |
| HT002         | HT002-2       |
| HT003         | HT003-2       |
+---------------+---------------+
3 rows in set (0.00 sec)

mysql>