Often encountered in development , Group query of the latest data , For example, the following table ( Query the latest record of each address )：
sql as follows ：
Uploading … Re upload cancel <>
-- ---------------------------- -- Table structure for test --
---------------------------- 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; -- ---------------------------- -- Records of test --
---------------------------- INSERT INTO `test` VALUES (1, ' Zhang San 1', ' Beijing ',
'2019-09-10 11:22:23'); INSERT INTO `test` VALUES (2, ' Zhang San 2', ' Beijing ', '2019-09-10
12:22:23'); INSERT INTO `test` VALUES (3, ' Zhang San 3', ' Beijing ', '2019-09-05 12:22:23');
INSERT INTO `test` VALUES (4, ' Zhang San 4', ' Beijing ', '2019-09-06 12:22:23'); INSERT INTO
`test` VALUES (5, ' Li Si 1', ' Shanghai ', '2019-09-06 12:22:23'); INSERT INTO `test`
VALUES (6, ' Li Si 2', ' Shanghai ', '2019-09-07 12:22:23'); INSERT INTO `test` VALUES (7,
' Li Si 3', ' Shanghai ', '2019-09-11 12:22:23'); INSERT INTO `test` VALUES (8, ' Li Si 4', ' Shanghai ',
'2019-09-12 12:22:23'); INSERT INTO `test` VALUES (9, ' WangTwo 1', ' Guangzhou ', '2019-09-03
12:22:23'); INSERT INTO `test` VALUES (10, ' WangTwo 2', ' Guangzhou ', '2019-09-04 12:22:23');
INSERT INTO `test` VALUES (11, ' WangTwo 3', ' Guangzhou ', '2019-09-05 12:22:23');
Uploading … Re upload cancel <>
Usually, we will arrange the flashbacks according to time and then group them , Get the latest record of each address ,sql as follows ：
SELECT * FROM(SELECT * FROM test ORDER BY create_time DESC) a GROUP BY address
But the query results are not what we want ：
The execution time is arranged by flashback, and the result is ：
So what you really want is id by 2/8/11 Records of , The query above shows that 1/5/9, Why is this ?
Because in mysql5.7 When , The sorting of subqueries has become invalid , It may be that most sub queries are used as a result for the main query , The reason why sub queries do not need sorting .
So how should we check it , There are two ways ：
First kind ：
SELECT * FROM(SELECT * FROM test ORDER BY create_time DESC LIMIT 10000) a
GROUP BY address
The result is ：
Sort sub queries limit limit , At this time, the sub query is not only sorting , So the sorting will take effect at this time , But the limit can only be set as large as possible
The second kind ：
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
adopt MAX Function to get the latest time and address （ Because you need to group by address ）, Then perform an associated query with the original data as a table ,
The condition is that the address and time should be equal to the maximum time and address obtained , At this time, the result is ：
The query efficiency of these two methods is not too poor , The second query is slightly faster than the first query , It may be that the sub query of the second method has only two fields ( time , Grouped fields ) Why !
If you are interested, you can query and compare according to a table with many fields and a large amount of data .
PS： The latest record in the second way , Not at the same time, the place and time are the same , If this happens , The second way is to find out these two records , And the first one won't .
So choose one of them according to the business and data conditions , After all, the efficiency is not too poor .
Split the sky and make land , Open up your own world !!! Encourage with you
Technology