如何从两个表中选择最新的结果?

I have two tables and I would like to join then with a query.

我有两个表，我想加入一个查询。

result save the actual entry of results

保存结果的实际条目

user_tracking tracks the acceptance and completion of work, users can cancel and accepts work again at a later time.

user_tracking tracking track the acceptance and completion of work, users can cancel and accept work at a later time。

SELECT *
from
svr1.result r, 
svr1.user_tracking u 
where
r.uid = u.user_id and r.tid = u.post1
and u.function_name = '7' #7 == accept work
and r.insert_time > '2015-09-23 00:00:00' and r.insert_time < '2015-10-03 00:00:00' 
and u.track_time > '2015-09-23 00:00:00' and u.track_time < '2015-10-03 00:00:00'

my result table had 1785 records within the period I wanted to track but the above query returns 1990 records. I would like to know how can i filter to get the latest date accepted by user only.

我的结果表在我想要跟踪的时间段内有1785条记录，但是上面的查询返回1990条记录。我想知道如何过滤，以获得用户只接受的最新日期。

in result table: uid,INT, tid,INT, result,VARCHAR and insert_time,TIMESTAMP

结果表:uid、INT、tid、result、VARCHAR和insert_time、时间戳

in user_tracking table: user_id,INT, post1,VARCHAR function_name,VARCHAR, result,VARCHAR and track_time,TIMESTAMP

user_tracking table: user_id、INT、post1、VARCHAR function_name、VARCHAR、result、VARCHAR和track_time、时间戳

the user_tracking function sample records, in this query the track time will change and the rest will remain the same.

user_tracking函数示例记录，在此查询中，跟踪时间将改变，其余的将保持不变。

1 个解决方案

#1

Use the GROUP BY command with a MAX() on the required date, this will select the latest date of all the options (assuming all the other columns are equal). Code as follows (need to declare all columns because of the MAX unfortunately):

在需要的日期使用GROUP BY命令并使用MAX()，这将选择所有选项的最新日期(假设所有其他列都相等)。代码如下(由于MAX的原因需要声明所有列):

SELECT r.uid,
    r.tid,
    r.result,
    r.insert_time,
    u.user_id,
    u.post1,
    u.function_name,
    u.result,
    MAX(track_time)        
FROM
svr1.result r, 
svr1.user_tracking u 
WHERE
r.uid = u.user_id AND r.tid = u.post1
AND u.function_name = '7' #7 == accept work
AND r.insert_time > '2015-09-23 00:00:00' AND r.insert_time < '2015-10-03 00:00:00' 
AND u.track_time > '2015-09-23 00:00:00' AND u.track_time < '2015-10-03 00:00:00'
GROUP BY
    r.uid,
    r.tid