时间戳

首页 > 解决方案 > 从可能对应日期的 2 个表中选择

mysql - 从可能对应日期的 2 个表中选择

问题描述

我正在为我的 mysql 数据库寻找正确的查询，该数据库有 2 个用于长度和权重的单独表。我希望将结果作为 1 个查询返回，其中包含 3 列：日期时间、长度和重量。查询还应该允许指定用户。例如。：

桌子高度：

id  user_id  created_on           height 
 1  2        2019-01-01 00:00:01  180  
 2  2        2019-01-02 00:00:01  181  
 3  3        2019-01-03 00:00:01  182  
 4  3        2019-01-04 00:00:01  183  
 5  2        2019-01-07 00:00:01  184

表重量：

 id  user_id  created_on           weight
 1   2        2019-01-01 00:00:01  80   
 2   2        2019-01-04 00:00:01  81   
 3   3        2019-01-05 00:00:01  82   
 4   3        2019-01-06 00:00:01  83   
 5   2        2019-01-07 00:00:01  84

我希望通过单个查询获得以下结果：

user_id  created_on             weight  height 
2        2019-01-01 00:00:01    80      180  
2        2019-01-02 00:00:01    null    181  
2        2019-01-04 00:00:01    81      null  
2        2019-01-07 00:00:01    84      184

我曾尝试使用 JOIN 语句，但未能获得所需的结果。这个加入声明

SELECT w.* , h.* FROM weight w 
  JOIN height h 
    ON w.created_on=h.created_on
    AND w.user_id=h.user_id AND user_id=2

将仅返回那些同时具有 user_id 和 created_on 的身高和体重项目的结果。完全外部联接可以解决问题，但是 mysql 不支持。以下查询似乎返回了所需的结果，但是速度很慢：

SELECT r.* FROM 
    (SELECT w.user_id as w_user, w.created_on as weightdate, w.value as weight, h.created_on as heightdate ,h.user_id as h_user, h.value as height FROM weight w
    LEFT JOIN height h ON w.user_id = h.user_id 
    AND w.created_on=h.created_on 
    UNION 
    SELECT w.user_id as w_user, w.created_on as weightdate, w.value as weight, h.created_on as heightdate ,h.user_id as h_user, h.value as height FROM weight w
    RIGHT JOIN height h ON w.user_id = h.user_id 
    AND w.created_on=h.created_on ) r 
WHERE h_user=2 OR w_user =2

如果 2 个表有大约 3000 个条目，则查询需要 3 秒以上。有没有办法加快速度，可能使用不同的方法？

对于额外的奖励积分：是否可以允许两个 created_on 日期时间之间存在小的时间差异？（例如 10 分钟或同一小时内）。例如。如果列 weight 有一个 2019-01-01 00:00:00 的条目，并且 table height 在 2019-01-01 00:04:00 有一个高度条目，它们会出现在同一行中。

标签： mysql

解决方案

UNION您可以使用 a从heights和表中选择所有不同的日期，而不是使用日历表来选择感兴趣的日期weights。要处理彼此在一小时内的匹配时间，您可以使用比较时间TIMESTAMPDIFF并将时间截断created_on为小时。由于这可能会创建重复条目，因此我们将DISTINCT限定符添加到查询中：

SELECT DISTINCT COALESCE(h.user_id, w.user_id) AS user_id,
       DATE_FORMAT(COALESCE(h.created_on, w.created_on), '%y-%m-%d %H:00:00') AS created_on,
       w.weight,
       h.height
FROM (SELECT created_on FROM heights
      UNION
      SELECT created_on FROM weights) d
LEFT JOIN heights h ON ABS(TIMESTAMPDIFF(HOUR, h.created_on, d.created_on)) = 0 AND h.user_id = 2
LEFT JOIN weights w ON ABS(TIMESTAMPDIFF(HOUR, w.created_on, d.created_on)) = 0 AND w.user_id = 2
WHERE h.user_id IS NOT NULL OR w.user_id IS NOT NULL
ORDER BY created_on

输出（来自我的演示，我修改了您的时间以允许在一小时内匹配）：

user_id     created_on          weight  height
2           19-01-01 01:00:00   80      180
2           19-01-02 00:00:00           181
2           19-01-04 04:00:00   81  
2           19-01-07 06:00:00   84      184

dbfiddle 上的演示

推荐阅读