从两个表中选择时如何按日期获取最新结果? [英] How to get latest results by date when selecting from two table?

问题描述

我有两个表，然后我想通过查询加入.

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

result保存结果的实际条目

user_tracking跟踪工作的接受和完成，用户可以在以后取消并再次接受工作.

user_tracking tracks the acceptance and completion of work, users can cancel and accepts work again 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'

我的result表在我要跟踪的时间内有1785条记录 但是上面的查询返回了1990条记录.我想知道如何过滤以获取仅由用户接受的最新日期.

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.

:uid，INT，tid，INT，结果，VARCHAR和insert_time，TIMESTAMP

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

:user_id，INT，post1，VARCHAR函数名，VARCHAR，结果，VARCHAR和track_time，TIMESTAMP

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

user_tracking函数样本记录，在此查询中，跟踪时间将更改，其余时间将保持不变.

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

推荐答案

在要求的日期使用GROUP BY命令和MAX()，这将选择所有选项的最新日期(假设所有其他列均为平等的).代码如下(不幸的是，由于MAX而需要声明所有列):

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):

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      

这篇关于从两个表中选择时如何按日期获取最新结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！