How to use sql_ to query the current status of each tid

This article mainly introduces how to use sql_ to query the current status of each tid, the article is very detailed, has a certain reference value, interested friends must read it!

I. problem scenario

There are key fields such as tid,action,dateline in a table. Tid represents the id,action of the post represents the status of the post being manipulated, and dateline represents the timestamp of the data being inserted.

In this table, each tid has several details inserted by different dateline, and the action field in the last time insert indicates the post status of the current tid.

Now you want to query the current status of each tid, that is, the newly published record

Data samples in the table:

Second, sql statement: select a.* from pre_forum_threadmod as a, (select tid,max (dateline) as dateline from pre_forum_threadmod group by tid) as bwhere a.tid=b.tid and a.dateline=b.dateline III. Sql sentence parsing:

In the above sql statement, we first find out the largest dateline of each tid, and the query generates a temporary table b

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

Then associate the query with the original table an and the temporary table b:

Where a.tid=b.tid and a.dateline=b.dateline

After the query, as shown in the following figure, the latest record of each tid can be found:

The above is all the contents of the article "how to use sql_ to query the current status of each tid". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!