MySQL Group按ID和最新日期时间 [英] MySQL Group by ID and Latest Datetime

问题描述

我在这里看到了类似类型的问题，但没有一个帮助我解决问题.

I saw similar type of questions here but none of them helped my situation.

我有一个表可以说是Test(使用mysql MyISAM)

I have a table lets say Test (using mysql MyISAM)

测试具有以下架构:

    TID INT PRIMARY KEY AUTO_INCREMENT
    TData varchar (data that i want to select)
    TUserID INT (this will be joined with users table, same users can have many records here)
    TViewedAt DATETIME (records each time user visits page, datetime format)

现在，当前登录的用户每次访问测试页时，都会向该表添加记录，以记录访问过的用户ID，每次访问时带有TID的某些数据以及日期和时间都会自动递增.

Now each time a test page is visited by currently logged in user, it adds records to this table recording the user ID whoever visited, some data and date and time that user visited with TID being auto incremented each time.

然后我有一个后端管理页面，在这里我可以看到哪个用户访问了测试页面，多少次，数据以及何时.基本上，它是一个包含20-25个最新行的列表的表.我的查询应该为每个数据选择一个记录.用户的数据可以相同，但时间不同，但是我只想为每个用户选择一个最新的数据.因此，如果用户访问测试页10次，则10条记录进入数据库，但表上仅显示1条最新记录.如果另一个用户访问该页面15次，它将为该第二个用户显示1条记录，这又是最新记录，因此，总共共有2行显示在表上.我可以进行所有工作，但是GROUP BY出于某种原因，并且MAX(date)没有为我提供每个日期的最新日期时间.

Then i have a backend manage page where i can see which user visited test page, how many times, data and when. Basically its a table with list of 20-25 latest rows. My Query should only select one record per data. Data can be same for user but at different time but i only want to select one data for each user which is the most latest. So if user visited test page 10 times, 10 records go to database but displays only 1 latest record on the table. If another user visits page 15 times it displays 1 record for this second user which is the latest one again so in total now we have 2 rows displaying on the table. I got everything to work but GROUP BY for some reason and MAX(date) does not give me the latest datetime for each date.

这是我的查询:

   SELECT *
   FROM Test
   WHERE TUserID = 123
   GROUP BY TData
   ORDER BY TViewedAt

返回2行，其中所有行都不是最新的.

returns 2 rows out of which none of those rows are the latest.

非常感谢

推荐答案

我认为，通过以下操作，您可以实现所需的功能，这些操作称为逐组最大值".

I think that with the below you can achieve what you need, These operation are so called "Group-wise Maximum".

选项1

这是最容易理解的，子查询将为所有用户返回最大值TID，因为max与Group By一起使用，并且比我们执行另一个查询来获取这些ID的所有数据.

This is the easiest to understand, a subquery will return maximum TID for all users since the max is used in together with Group By and than we do an other query to get all the data for those IDs.

 Select TID, TData, TUserID, TViewedAt
 From Test 
 Where TID In(
    Select Max(TID)
    From Test
    Group By TUserID
)

选项2

理解起来稍微复杂一点，但是更有可能更有效，它的工作原理是，当t1.TViewedAt达到最大值时，没有t2.TViewedAt具有更大的值，并且t2行的值将是.

Slightly more complex to understand, but most likely more efficient, This works on the basis that when t1.TViewedAt is at its maximum value, there is no t2.TViewedAt with a greater value and the t2 rows values will be NULL.

SELECT t1.TID, t1.TData, t1.TUserID, t1.TViewedAt
FROM Test t1
LEFT JOIN Test t2 ON t1.TUserID = t2.TUserID AND t1.TViewedAt < t2.TViewedAt
WHERE t2.TUserID IS NULL;

结果

TID TData   TUserID   TViewedAt
4   test3   123       2012-10-05 00:00:00.000
5   test2   213       2012-10-03 00:00:00.000

这篇关于MySQL Group按ID和最新日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！