sql_Query the current status of each tid: that is, the latest record published by the category

1. Problem Scenario

1. There are key fields in a table such as tid, action, dateline, etc. tid represents the id of the post, action represents the status of the post being operated, and dateline represents The timestamp when the data was inserted;

2. In this table, each tid will have multiple details inserted by different datelines. The action field in the details inserted at the last time represents the current tid. Post status;

3. Now I want to query the current status of each tid, that is, query the latest published record

4. Data sample in the table ：

   
   

2. SQL statement:

select a.* from pre_forum_threadmod as a,(select tid,max(dateline) as dateline from pre_forum_threadmod group by tid) as b
where a.tid=b.tid and a.dateline=b.dateline

3. SQL statement Analysis:

1. In the above sql statement, first find the largest dateline of each tid, and query to generate a temporary table b;

   (select tid,max(dateline) as dateline from pre_forum_threadmod group by tid) as b

2. Then the original table a can be associated with the temporary table b and queried:

   where a.tid=b.tid and a.dateline=b.dateline

3. After querying, as shown below, the latest record of each tid can be found:
   
   

Related articles:

Query the first few records of each group after grouping

MySQL Query the first few records of different categories in the same table

Related video:

SQL introductory tutorial manual

The above is the detailed content of sql_Query the current status of each tid: that is, the latest record published by the category. For more information, please follow other related articles on the PHP Chinese website!