代码之家 › 专栏 › 技术社区 › Conrad M

MySQL为不存在的记录返回null

union join select database mysql

Conrad M · 技术社区 · 7 年前

我有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)

测试中的时间戳。表一(例如)每十五分钟一次。

全部的 带有“SomeField”列的时间戳显示对应日期时间的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)

任何帮助都将不胜感激。谢谢

1 回复 | 直到 7 年前

carla Sergey Berezovskiy 7 年前

如果只使用一行时间戳和两个列以及每个表中的值,则可以使用较小的查询。

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