在开发过程中,我们经常会遇到需要查询分组最新数据的问题。例如,下面的表格中,我们需要查询每个地址的最新一条记录:
```sql
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY address ORDER BY create_time DESC) AS row_num
FROM your_table
) AS temp
WHERE temp.row_num = 1;
```
在这个例子中,我们使用了`ROW_NUMBER()`窗口函数来为每个分组(这里是按地址分组)的记录分配一个行号。然后,我们只选择行号为1的记录,即每个分组中的最新记录。请将`your_table`替换为你的实际表名。
```sql
-- 创建测试表
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`address` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 插入测试记录
INSERT INTO `test` VALUES (1, '张三1', '北京', '2019-09-10 11:22:23');
INSERT INTO `test` VALUES (2, '张三2', '北京', '2019-09-10 12:22:23');
INSERT INTO `test` VALUES (3, '张三3', '北京', '2019-09-05 12:22:23');
INSERT INTO `test` VALUES (4, '张三4', '北京', '2019-09-06 12:22:23');
INSERT INTO `test` VALUES (5, '李四1', '上海', '2019-09-06 12:22:23');
INSERT INTO `test` VALUES (6, '李四2', '上海', '2019-09-07 12:22:23');
INSERT INTO `test` VALUES (7, '李四3', '上海', '2019-09-11 12:22:23');
INSERT INTO `test` VALUES (8, '李四4', '上海', '2019-09-12 12:22:23');
INSERT INTO `test` VALUES (9, '王二1', '广州', '2019-09-03 12:22:23');
INSERT INTO `test` VALUES (10, '王二2', '广州', '2019-09-04 12:22:23');
INSERT INTO `test` VALUES (11, '王二3', '广州', '2019-09-05 12:22:23');
```
根据提供的内容,我们可以重构如下:
在MySQL 5.7中,子查询的排序已经变为无效。因此,我们需要找到一种方法来获取id为2/8/11的记录。有两种方法可以实现这个目标:
第一种方法:
```sql
SELECT * FROM (SELECT * FROM test ORDER BY create_time DESC LIMIT 10000) a GROUP BY address
```
这种方法对子查询的排序进行限制,此时子查询不仅仅是排序,所以排序会生效。但是,限制条数只能尽可能地设置大一些。
第二种方法:
```sql
SELECT t.* FROM (SELECT address, max(create_time) as create_time FROM test GROUP BY address) a LEFT JOIN test t ON t.address = a.address AND t.create_time = a.create_time
```
通过MAX函数获取最新的时间和地址(因为需要按照地址分组),然后将这张表与原始数据进行联查。条件是地址和时间必须与获取的最大时间和地址相等。这样,结果将只包含最新的记录。需要注意的是,第二种方法中最新的记录不能同时具有相同的地点和时间,否则第二种方法会将这两条记录都查出来,而第一条不会。
根据业务和数据情况选择其中一种方法,因为它们的效率相差不大。