获取比较两个表中日期时间字段的最新数据 [英] Get the latest data comparing datetime fields from two tables

问题描述

我有两个具有相同结构的表:

I have two tables with identical structure like this:

表:用户

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |    abu123NEW |  2014-06-04 14:55:06 |
|  2 | john   |      john123 |  2014-06-04 14:58:22 |
|  3 | shane  |     shane123 |  2014-06-04 15:02:06 |
|  4 | marie  |     marie123 |  2014-06-04 15:00:06 |
|  5 | mike   |   mike123NEW |  2014-06-04 15:01:32 |
|  6 | kiron  |  kiron123NEW |  2014-06-04 15:05:46 |
+----+--------+--------------+----------------------+

表: user_k

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |       abu123 |  2014-06-04 14:53:06 |
|  2 | john   |   john123NEW |  2014-06-04 14:59:48 |
|  3 | shane  |  shane123NEW |  2014-06-04 15:00:06 |
|  4 | marie  |  marie123NEW |  2014-06-04 15:03:17 |
|  5 | mike   |      mike123 |  2014-06-04 15:00:36 |
|  6 | kiron  |     kiron123 |  2014-06-04 15:02:18 |
+----+--------+--------------+----------------------+

现在，我需要从这两个表中获取最新数据(通过"LastUpdateTime")，如下所示:

Now I need to fetch the latest data (by 'LastUpdateTime') from these two tables, like this:

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |    abu123NEW |  2014-06-04 14:55:06 |
|  2 | john   |   john123NEW |  2014-06-04 14:59:48 |
|  3 | shane  |     shane123 |  2014-06-04 15:02:06 |
|  4 | marie  |  marie123NEW |  2014-06-04 15:03:17 |
|  5 | mike   |   mike123NEW |  2014-06-04 15:01:32 |
|  6 | kiron  |  kiron123NEW |  2014-06-04 15:05:46 |
+----+--------+--------------+----------------------+

我尝试过的事情:

SELECT uk.* FROM user AS ua, user_k AS uk
WHERE  uk.ID = ua.ID AND uk.LastUpdateTime > ua.LastUpdateTime

UNION

SELECT ua.* FROM user AS ua ,user_k AS uk
WHERE  uk.ID = ua.ID AND uk.LastUpdateTime < ua.LastUpdateTime

我不确定这是否合适.我也担心大量数据的查询性能.有没有更好的方法呢?

I'm not sure if this is the appropriate way. Also I'm concerned about the query performance with large set of data. Is there any better approach for this?

PS :除了名称&密码在两个表中都有十几个字段(列).我只是为了简化问题而避免使用它们.

推荐答案

select
u.id,
u.name,
if(u.LastUpdateTime >= k.LastUpdateTime, u.password, k.password) as password,
greatest(u.LastUpdateTime, k.LastUpdateTime) as LastUpdateTime
from
user u
inner join user_k k on u.id = k.id

• 在 sqlfiddle 中实时查看它的运行情况

P.S .: 对于ID 3，较新的密码实际上是用户表中的密码，而不是user_k.您想要的结果集需要进行此调整.

这篇关于获取比较两个表中日期时间字段的最新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！