©2020-2024 ioDraw All rights reserved
Do business at ordinary times , It is often the latest piece of data that needs to be checked .
As for the latest concept , For products , What I often say is Chronological order , The latest is Recent meaning .
Combined examples ：
This is a record sheet for recording the visits of personnel .
The data in the data sheet accurately records the color of the hat everyone wears when visiting , time , Personnel code （ Everyone is unique ）.
Sample data ：
What needs to be done is ：
Take out the latest visit records that meet the conditions .
What would you do best ?
Realize a little first , take out A101 This person is coded Latest visit records .
First show the wrong sql Example ： Take it for granted max() function .
SELECT MAX(id) AS id ,user_code,cap_color,create_time FROM vist_record WHERE
user_code='A101' ;
Query results （ Wrong result ）：
Obviously, at first glance, the data are similar .
But it's actually wrong , Yes, filter by criteria , And then take the largest one that meets the conditions id value , Replaced separately id.
The correct data is ：
Is that right max（id） It's useless ?
Correct usage （ Will meet the conditions of the maximum id Value as condition ）：
SELECT
id,user_code,cap_color,create_time
FROM vist_record
WHERE id IN (SELECT MAX(id) AS id FROM vist_record WHERE user_code='A101' )
Query results ：
But see the above method of using subqueries ,
Everyone must have been secretly scolding their mother , It's so troublesome to get the latest data ?
Do you have something simpler ?
have .
for instance , We have determined , id Is self increasing ,id Largest data （ Eligible data ） It's the latest .
Then we can use reverse order DESC To get the latest data ：
DESC that is Reverse order / Descending order .
PS：
Use reverse lookup ：
SELECT *
FROM vist_record
WHERE user_code='A101'
ORDER BY id DESC
LIMIT 1;
Query results ：
Or in reverse chronological order ：
SELECT *
FROM vist_record
WHERE user_code='A101'
ORDER BY create_time DESC
LIMIT 1;
Query results ：
Is it that simple ?
So if what we need is not specified A101 What we need is the latest data of everyone involved ?
That is, there are multiple groups .
The latest qualified data of each category
The orange box is A101 ,B202 , C303 Separate latest records , We need to take it out .
Error examples ：
SELECT MAX(id) AS id ,user_code,cap_color,create_time FROM vist_record GROUP
BY user_code
Bad filter results ：
Correct coding ：
SELECT id ,user_code,cap_color,create_time FROM vist_record WHERE id in
(
SELECT MAX(id) AS id FROM vist_record GROUP BY user_code
)
okay , Let's stop here first .
Technology