时间戳

首页 > 解决方案 > SQL：列出没有任何事务的行

mysql - SQL：列出没有任何事务的行

问题描述

我有一个定义表 - def

Id        Device            Location
1         GGHY199           USA
12        DFGHY71           India
145       APPHY75           USA

并且它的交易记录在一个差异表中 - 事件

eventid        deviceid         event                date
123              12             Login              12-01-2019
32               12             Unreachable        18-02-2019
223              145            Unreachable        19-02-2019
334              1              DOWN               01-03-2019

我想要一个每天的输出，所有三个设备都应该显示，如果它没有事务，它应该显示为空，我假设是日期列中月份的第一个日期。喜欢，

eventid        deviceid         event                date
null             1              null               01-01-2019
123              12             Login              12-01-2019
null             145            null               01-01-2019
null             1              null               01-02-2019
32               12             Unreachable        18-02-2019
223              145            Unreachable        19-02-2019
334              1              DOWN               01-03-2019
null             12             null               01-03-2019
null             145            null               01-03-2019

目前我在做：

select * from def
left join 
event on def.id=event.deviceid

我显然没有得到我想要的。

谢谢！

标签： mysqlsql

解决方案

你似乎在追求这样的事情......

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(event_id SERIAL PRIMARY KEY
,device_id INT NOT NULL
,event VARCHAR(12) NOT NULL
,date DATE NOT NULL
);

INSERT INTO my_table VALUES
(123, 12,'Login','2019-01-12'),
( 32, 12,'Unreachable','2019-02-18'),
(223,145,'Unreachable','2019-02-19'),
(334,  1,'DOWN','2019-03-01');

 SELECT DISTINCT z.event_id
               , x.device_id
               , z.event
               , y.date 
            FROM my_table x 
            JOIN my_table y 
            LEFT 
            JOIN my_table z 
              ON z.device_id = x.device_id 
             AND z.date = y.date 
           ORDER 
              BY date
               , device_id;
+----------+-----------+-------------+------------+
| event_id | device_id | event       | date       |
+----------+-----------+-------------+------------+
|     NULL |         1 | NULL        | 2019-01-12 |
|      123 |        12 | Login       | 2019-01-12 |
|     NULL |       145 | NULL        | 2019-01-12 |
|     NULL |         1 | NULL        | 2019-02-18 |
|       32 |        12 | Unreachable | 2019-02-18 |
|     NULL |       145 | NULL        | 2019-02-18 |
|     NULL |         1 | NULL        | 2019-02-19 |
|     NULL |        12 | NULL        | 2019-02-19 |
|      223 |       145 | Unreachable | 2019-02-19 |
|      334 |         1 | DOWN        | 2019-03-01 |
|     NULL |        12 | NULL        | 2019-03-01 |
|     NULL |       145 | NULL        | 2019-03-01 |
+----------+-----------+-------------+------------+

推荐阅读