我在表用户中的用户的日志表中获得了最大值"值",我的查询似乎有效,但它拾取了最大值,但错误的datatime列数据
表用户
(primary+
unique)
name | current_list | other_columns
-----------------------------------------
george 1 data
carl 2 data
paul 1 data
表日志
user | list | value | datatime
-------------------------------------
george 1 4 2018-01-01
paul 1 7 2018-01-02
carl 2 3 2018-01-03
george 1 5 2018-01-04
paul 2 5 2018-01-05
carl 2 6 2018-01-06
carl 3 8 2018-01-07
george 2 9 2018-01-08
paul 1 8 2018-01-09
george 1 3 2018-01-10
结果应为(列表选择" Current_List"的最高值(
user | other_columns | top | datatime
---------------------------------------------
george data 5 2018-01-04
paul data 8 2018-01-09
carl data 6 2018-01-06
当我尝试显示DataTime列时,我得到了错误的数据
SELECT Logs.datatime,
Users.*,
MAX(Logs.value)
FROM Users, Logs
WHERE Users.current_list = Logs.list
and Users.name = Logs.user
group by Logs.user
可能没有子查询解决问题吗?还是我需要使用它?如何?
考虑我有一个巨大的数据库,因此,快速查询更好
首先,您需要找到每个值的最大价值。
SELECT U.name, U.current_list, MAX(L.value) as m_value
FROM Users U
JOIN Logs U
ON U.name = L.user
AND U.current_list = L.list
GROUP BY U.name, U.current_list
然后获取其余的字段
SELECT U.*, L.Value
FROM Users U
JOIN Logs U
ON U.name = L.user
AND U.current_list = L.list
JOIN ( SELECT U.name, U.current_list, MAX(L.value) as m_value
FROM Users U
JOIN Logs U
ON U.name = L.user
AND U.current_list = L.list
GROUP BY U.name, U.current_list
) T
ON U.name = T.name
AND U.current_list = L.current_list
AND L.value= T.m_value