The source data is as follows:
The desired effect is to get the latest data based on the TARGET_ID group:
Two scenarios are now being thought of:
Scenario One: Find the largest data ID by using the same table subquery or co-search
There is a kind of wording:
select * from (select * from track where type='task' and target_id in(...) ORDER BY time DESC) as temp GROUP BY target_id
Scheme two: Two-step query, PHP first query the maximum ID, and then through the ID array to query the list of data
I would like to ask is there any other simple way to deal with this problem?
This need should be more common!
===== attached structure and data =====
DROP TABLE IF EXISTS ' track '; CREATE TABLE ' track ' (' id ' int (one) ' not ' null ' auto_increment, ' type ' varchar () ' ' ' " Task follow-up, Project + projects follow up ', ' target_id ' int (one) default ' 0 ' COMMENT ' follow-up target id ', ' user_id ' int (one) default ' 0 ' COMMENT ' follow-up user ', ' user_name ' varchar (+) Default ' COMMENT ' follow up user name ', ' content ' varchar ($) Default ' COMMENT ' follow up ', ' time ' int (1 1) Default ' 0 ' COMMENT ' follow-up time ', PRIMARY KEY (' id ')) engine=innodb auto_increment=10 default Charset=utf8 comment= ' follow-up record table ';- -------------------------------Records of track------------------------------INSERT to ' track ' VALUES (' 1 ', ' Task ', ' 67 ', ' 1 ', ' super admin ', ' doesn't matter ... ', ' 1467774850 '); INSERT into ' track ' VALUES (' 2 ', ' Task ', ' 67 ', ' 1 ', ' super admin ', ' tttt ', ' 14677776 Insert INTO ' track ' Values (' 7 ', ' Task ', ' 67 ', ' 1 ', ' super admin ', ' only wear ', ' 1468288894 '); insert into ' track ' Values (' 8 ', ' t Ask ', ' 34 ', ' 1 ', ' super admin ', ' STS ', ' 1468288917 '); INSERT into ' track ' VALUES (' 9 ', ' Task ', ' 34 ', ' 1 ', ' super admin ', ' ceiling ', ' 1468288954 ');
Reply content:
The source data is as follows:
The desired effect is to get the latest data based on the TARGET_ID group:
Two scenarios are now being thought of:
Scenario One: Find the largest data ID by using the same table subquery or co-search
There is a kind of wording:
select * from (select * from track where type='task' and target_id in(...) ORDER BY time DESC) as temp GROUP BY target_id
Scheme two: Two-step query, PHP first query the maximum ID, and then through the ID array to query the list of data
I would like to ask is there any other simple way to deal with this problem?
This need should be more common!
===== attached structure and data =====
DROP TABLE IF EXISTS ' track '; CREATE TABLE ' track ' (' id ' int (one) ' not ' null ' auto_increment, ' type ' varchar () ' ' ' " Task follow-up, Project + projects follow up ', ' target_id ' int (one) default ' 0 ' COMMENT ' follow-up target id ', ' user_id ' int (one) default ' 0 ' COMMENT ' follow-up user ', ' user_name ' varchar (+) Default ' COMMENT ' follow up user name ', ' content ' varchar ($) Default ' COMMENT ' follow up ', ' time ' int (1 1) Default ' 0 ' COMMENT ' follow-up time ', PRIMARY KEY (' id ')) engine=innodb auto_increment=10 default Charset=utf8 comment= ' follow-up record table ';- -------------------------------Records of track------------------------------INSERT to ' track ' VALUES (' 1 ', ' Task ', ' 67 ', ' 1 ', ' super admin ', ' doesn't matter ... ', ' 1467774850 '); INSERT into ' track ' VALUES (' 2 ', ' Task ', ' 67 ', ' 1 ', ' super admin ', ' tttt ', ' 14677776 Insert INTO ' track ' Values (' 7 ', ' Task ', ' 67 ', ' 1 ', ' super admin ', ' only wear ', ' 1468288894 '); insert into ' track ' Values (' 8 ', ' t Ask ', ' 34 ', ' 1 ', ' super admin ', ' STS ', ' 1468288917 '); INSERT into ' track ' VALUES (' 9 ', ' Task ', ' 34 ', ' 1 ', ' super admin ', ' ceiling ', ' 1468288954 ');
Put the data of the maximum ID into the temporary table
CREATE TEMPORARY TABLE tmp_id(`id` int(11) not null,PRIMARY KEY (`id`) )
And then
INSERT INTO tmp_id SELECT max(`id`) as id FROM track GROUP BY target_id
And then join a little bit.
Finally, it is recommended that you explicitly delete the temporary table
DROP TEMPORARY TABLE IF EXISTS tmp_id
I think the plan 1A is good, but why target_id also write in conditions, do not write is the same result.
is generally used in Scenario 1, Scenario 1 has another way of writing
select * from track where id in(select substring_index(group_concat(id order by id desc),',',1) as maxid from track group by target_id);