MySQL 按 ID 和最新日期时间分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12752375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Group by ID and Latest Datetime
提问by Giorgi
I saw similar type of questions here but none of them helped my situation.
我在这里看到了类似的问题，但没有一个对我的情况有帮助。
I have a table lets say Test (using mysql MyISAM)
我有一个表可以说测试（使用 mysql MyISAM）
Test has the following schema:
测试具有以下架构：
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)
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.
现在，每次当前登录的用户访问测试页面时，它都会向该表添加记录，记录访问过的用户 ID、用户访问的一些数据以及日期和时间，每次 TID 都会自动递增。
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.
然后我有一个后端管理页面，我可以在其中查看哪个用户访问了测试页面、访问了多少次、数据和时间。基本上它是一个包含 20-25 个最新行列表的表格。我的查询应该只为每个数据选择一条记录。用户的数据可以相同但时间不同，但我只想为每个用户选择一个最新的数据。因此，如果用户访问测试页面 10 次，则有 10 条记录进入数据库，但表上仅显示 1 条最新记录。如果另一个用户访问页面 15 次，它会显示第二个用户的 1 条记录，这又是最新的，所以现在我们总共有 2 行显示在表上。我一切正常，但由于某种原因 GROUP BY 和 MAX(date) 没有给我每个日期的最新日期时间。
here is my query:
这是我的查询：
SELECT *
FROM Test
WHERE TUserID = 123
GROUP BY TData
ORDER BY TViewedAt
returns 2 rows out of which none of those rows are the latest.
返回 2 行，其中没有任何行是最新的。
thanks alot
多谢
回答by A. Agius
I think that with the below you can achieve what you need, These operation are so called "Group-wise Maximum".
我认为，通过下面的操作，您可以实现您所需要的，这些操作被称为“Group-wise Maximum”。
Option 1
选项1
This is the easiest to understand, a subquery will return maximum TIDfor all users since the maxis used in together with Group Byand than we do an other query to get all the data for those IDs.
这是最容易理解的，子查询将为TID所有用户返回最大值，因为它max与Group By其他查询一起使用，然后我们执行其他查询来获取这些 ID 的所有数据。
Select TID, TData, TUserID, TViewedAt
From Test
Where TID In(
Select Max(TID)
From Test
Group By TUserID
)
Option 2
选项 2
Slightly more complex to understand, but most likely more efficient, This works on the basis that when t1.TViewedAtis at its maximum value, there is no t2.TViewedAtwith a greater value and the t2 rows values will be NULL.
理解起来稍微复杂一点，但很可能更有效，这是基于当t1.TViewedAt处于最大值时，没有t2.TViewedAt更大的值并且 t2 行值将是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;
Result
结果
TID TData TUserID TViewedAt
4 test3 123 2012-10-05 00:00:00.000
5 test2 213 2012-10-03 00:00:00.000
回答by Michael Berkowski
You need to GROUP BY TUserIDfor the max aggregate.
您需要GROUP BY TUserID为最大聚合。
SELECT
TID,
TData,
main.TUserID
main.TViewedAt
FROM
yourTable main
JOIN (
SELECT TUserID, MAX(TViewedAt) AS TViewedAT FROM yourTable GROUP BY TUserID
) tmax ON main.TUserID = tmax.TUserID AND main.TViewedAt = tmax.TViewedAt
This is a portable method which joins against a subquery. The subquery pulls the latest TUserID,TViewedAtper user, and that is joined back against the main table to pull the TDatathat matches.
这是一个可移植的方法，它连接到子查询。子查询提取TUserID,TViewedAt每个用户的最新信息，并将其与主表连接以提取TData匹配项。
If you only want one user at a time, you can do:
如果您一次只需要一个用户，您可以执行以下操作：
SELECT
TData,
TViewedAt
FROM yourTable
WHERE TUserID = 'someid'
GROUP BY TUserId
HAVING TViewedAt = MAX(TViewedAt)
The above will only work in MySQL the way I have written it though. Other RDBMS will complain about the fact that TDataappears in the SELECTlist but is not in the GROUP BY.
以上只适用于我编写它的方式在 MySQL 中。其他 RDBMS 会抱怨TData出现在SELECT列表中但不在GROUP BY.
回答by Akshaykumar Maldhure
Following query, may help you-
以下查询，可能对你有帮助——
select * from pages where date(create_date)=(select date(create_date) from pages order by create_date limit 1)
回答by alex
SELECT t1.TData, t1.TUserID, t1.TViewedAt
FROM Test t1
JOIN
(SELECT TUserID, MAX(TViewedAt)
FROM Test
GROUP BY TUserID) t2
ON t1.TUserID = t2.TUserID AND t1.TViewedAt=t2.TViewedAt
