MySQL左连接和右表空列

I have the following two MySQL tables which I need to join:

我需要加入以下两个MySQL表:

CREATE TABLE `tbl_L` (
  `datetime` datetime NOT NULL,
  `value` decimal(14,8) DEFAULT NULL,
  `series_id` int(11) NOT NULL,
  PRIMARY KEY (`series_id`,`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tbl_R` (
  `datetime` datetime NOT NULL,
  `value` decimal(14,8) DEFAULT NULL,
  `series_id` int(11) NOT NULL,
  PRIMARY KEY (`series_id`,`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I need to select all the dates and values from tbl_L, but also the values in tbl_R that have the same datetime as an entry in tbl_L. A trivial join, like so:

我需要从tbl_L中选择所有的日期和值，但也需要从tbl_R中选择与tbl_L中的条目具有相同日期时间的值。一个琐碎的连接，比如:

SELECT tbl_L.datetime AS datetime, tbl_L.value AS val_L, tbl_R.value AS val_R 
FROM tbl_L 
    LEFT JOIN tbl_R 
        ON tbl_L.datetime = tbl_R.datetime
WHERE 
   tbl_L.series_id = 1 AND tbl_R.series_id = 2 ORDER BY tbl_L.datetime ASC

Won't work because it will only return datetime that are both in tbl_L and tbl_R (because the right table is mentioned in the WHERE clause).

无法工作，因为它只返回tbl_L和tbl_R中的datetime(因为在WHERE子句中提到了正确的表)。

Modifying the query to look like this:

将查询修改为如下所示:

SELECT tbl_L.datetime AS datetime, tbl_L.value AS val_L, tbl_R.value AS val_R 
FROM tbl_L 
    LEFT JOIN tbl_R 
        ON tbl_L.datetime = tbl_R.datetime 
           AND tbl_R.series_id = 2 
           AND tbl_L.series_id = 1
ORDER BY tbl_L.datetime ASC;

Significantly slows it down (from a few milliseconds to a few long seconds).

显著减慢(从几毫秒到几秒)。

Edit: and also doesn't actually work. I will clarify what I need to achieve:

编辑:实际上也不工作。我将阐明我需要实现的目标:

Assume the following data in the tables:

在表中假设以下数据:

mysql> SELECT * FROM tbl_R;
+---------------------+------------+-----------+
| datetime            | value      | series_id |
+---------------------+------------+-----------+
| 2013-02-20 19:21:00 | 5.87000000 |         2 |
| 2013-02-20 19:22:00 | 5.90000000 |         2 |
| 2013-02-20 19:23:00 | 5.80000000 |         2 |
| 2013-02-20 19:25:00 | 5.65000000 |         2 |
+---------------------+------------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tbl_L;
+---------------------+-------------+-----------+
| datetime            | value       | series_id |
+---------------------+-------------+-----------+
| 2013-02-20 19:21:00 | 13.16000000 |         1 |
| 2013-02-20 19:23:00 | 13.22000000 |         1 |
| 2013-02-20 19:24:00 | 13.14000000 |         1 |
| 2013-02-20 19:25:00 | 13.04000000 |         1 |
+---------------------+-------------+-----------+
4 rows in set (0.00 sec)

Again, I need all entries in tbl_L joined with the entries in tbl_R that match in terms of datetime, otherwise NULL.

同样，我需要tbl_L中的所有条目与tbl_R中的条目结合，这些条目在datetime上进行匹配，否则为NULL。

My output should look like this:

我的输出应该是这样的:

+---------------------+-------------+-------------+
| datetime            | val_L       | val_R       |
+---------------------+-------------+-------------+
| 2013-02-20 19:21:00 | 13.16000000 | 5.870000000 |
| 2013-02-20 19:23:00 | 13.22000000 | 5.800000000 |
| 2013-02-20 19:24:00 | 13.14000000 | NULL        |
| 2013-02-20 19:25:00 | 13.04000000 | 5.650000000 |
+---------------------+-------------+-------------+

Thanks again!

再次感谢!

1 个解决方案

#1

You can get the data you want by moving only the condition for tbl_R into the join's ON clause like this:

只需将tbl_R的条件移动到join's ON子句中，就可以获得所需的数据:

SELECT tbl_L.datetime AS datetime, tbl_L.value AS val_L, tbl_R.value AS val_R 
FROM tbl_L 
LEFT JOIN tbl_R 
    ON tbl_L.datetime = tbl_R.datetime
        AND tbl_R.series_id = 2
WHERE 
tbl_L.series_id = 1 ORDER BY tbl_L.datetime ASC

Also, there is no index for the query to use on tbl_L. Adding an index on tbl_L.series_id will help the query's performance.

而且，tbl_L上没有查询要使用的索引。在tbl_L上添加索引。series_id将有助于查询的性能。

#1