MySQL 为不存在的记录返回 null

Question

我有 2 个 MySQL 表，我需要将它们作为派生表加入和访问。

一些虚拟行的基本示例:

CREATE DATABASE Test;
USE Test;

CREATE TABLE TableOne(
    Id INT auto_increment NOT NULL,
    SomeField FLOAT,
    Timestamp DATETIME,
        PRIMARY KEY(Id)
);

CREATE TABLE TableTwo(
    Id INT auto_increment NOT NULL,
    SomeField FLOAT,
    Timestamp DATETIME,
        PRIMARY KEY(Id)
);

INSERT INTO TableOne
    (Timestamp, SomeField)
VALUES
    ('2017-06-30 23:30:00', RAND()),
    ('2017-06-30 23:45:00', RAND()),
    ('2017-07-01 00:00:00', RAND()),
    ('2017-07-01 00:15:00', RAND()),
    ('2017-07-01 00:30:00', RAND()),
    ('2017-07-01 00:45:00', RAND()),
    ('2017-07-01 01:00:00', RAND()),    
    ('2017-07-01 01:15:00', RAND()),    
    ('2017-07-01 01:30:00', RAND());    


INSERT INTO TableTwo
    (Timestamp, SomeField)
VALUES
    ('2017-06-30 23:30:00', RAND()),
    ('2017-06-30 23:40:00', RAND()),
    ('2017-06-30 23:50:00', RAND()),
    ('2017-07-01 00:00:00', RAND()),
    ('2017-07-01 00:10:00', RAND()),
    ('2017-07-01 00:20:00', RAND()),
    ('2017-07-01 00:30:00', RAND()),
    ('2017-07-01 00:40:00', RAND()),
    ('2017-07-01 00:50:00', RAND()),
    ('2017-07-01 01:00:00', RAND()),
    ('2017-07-01 01:10:00', RAND()),
    ('2017-07-01 01:20:00', RAND()),
    ('2017-07-01 01:30:00', RAND());

为了阅读表格，我正在使用:

SELECT
    SomeField,
    Timestamp
FROM
    (
        SELECT
            SomeField,
            Timestamp
        FROM
            TableOne

        UNION

        SELECT
            SomeField,
            Timestamp
        FROM
            TableTwo    
    ) d1
WHERE
    d1.Timestamp BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 01:00:00'
ORDER BY
    d1.Timestamp;

此查询返回下表:

+------------+---------------------+
| SomeField  | Timestamp           |
+------------+---------------------+
|   0.380433 | 2017-07-01 00:00:00 |
| 0.00938889 | 2017-07-01 00:00:00 |
|   0.963191 | 2017-07-01 00:10:00 |
|   0.290852 | 2017-07-01 00:15:00 |
|   0.674658 | 2017-07-01 00:20:00 |
|   0.483715 | 2017-07-01 00:30:00 |
|   0.426091 | 2017-07-01 00:30:00 |
|   0.394602 | 2017-07-01 00:40:00 |
|   0.257901 | 2017-07-01 00:45:00 |
|   0.521865 | 2017-07-01 00:50:00 |
|   0.425519 | 2017-07-01 01:00:00 |
|  0.0112322 | 2017-07-01 01:00:00 |
+------------+---------------------+
12 rows in set (0.00 sec)

Test.TableOne 中的时间戳(例如)每十五分钟一次。

Test.TableTwo 中的时间戳(例如)每十分钟一次。

我希望能够做的是返回带有所有时间戳的派生表，其中“SomeField”列显示相应 DATETIME 的 Null。

一个示例(使用上面的表格)如下所示:

+------------+---------------------+
| SomeField  | Timestamp           |
+------------+---------------------+
|   0.380433 | 2017-07-01 00:00:00 |
| 0.00938889 | 2017-07-01 00:00:00 |
|   0.963191 | 2017-07-01 00:10:00 |
|   NULL     | 2017-07-01 00:10:00 |
|   0.290852 | 2017-07-01 00:15:00 |
|   NULL     | 2017-07-01 00:15:00 |
|   0.674658 | 2017-07-01 00:20:00 |
|   NULL     | 2017-07-01 00:20:00 |
|   0.483715 | 2017-07-01 00:30:00 |
|   0.426091 | 2017-07-01 00:30:00 |
|   0.394602 | 2017-07-01 00:40:00 |
|   NULL     | 2017-07-01 00:40:00 |
|   0.257901 | 2017-07-01 00:45:00 |
|   NULL     | 2017-07-01 00:45:00 |
|   0.521865 | 2017-07-01 00:50:00 |
|   NULL     | 2017-07-01 00:50:00 |
|   0.425519 | 2017-07-01 01:00:00 |
|  0.0112322 | 2017-07-01 01:00:00 |
+------------+---------------------+
18 rows in set (0.00 sec)

我尝试了多种不同的 JOIN 来实现这一点，但没有成功。

如有任何帮助，我们将不胜感激。谢谢。

Answer 1

如果您仅按时间戳使用一行，并使用每个表中的值的两列，您的查询可能会更小。

但是，如果您真的需要像您要求的那样输出，则需要模拟两个完整的外部连接并将它们联合起来:

select * from (
           select t1.SomeField,Timestamp from TableOne t1 left join TableTwo t2 using (Timestamp) union all 
           select t2.SomeField,Timestamp from TableOne t1 left join TableTwo t2 using (Timestamp) union all
           select t1.SomeField,Timestamp from TableOne t1 right join TableTwo t2 using (Timestamp) union all 
           select t2.SomeField,Timestamp from TableOne t1 right join TableTwo t2 using (Timestamp)

          ) tt where tt.Timestamp BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 01:00:00' order by Timestamp