Mysql 从多行中选择，除了列中的 Null，结果 : latest in one row

Question

我正在努力选择 MySql 中的 View 。传感器将数据插入 3 种类型的行中。无(空)、gpsdata+lqi+空和温度+电池+空。现在我需要一个查询来在一行中显示最新的数据/设备ID。

表:数据

id  deviceid    timestamp          temp gpslat  gpslng  lqi     battery
1   12C79E6     2019-11-18 19:49:30                                         (all Null)
2   12C79E6     2019-11-18 19:49:29     59.xxx  18.xxx  Good                (gpsdata+lqi)
3   3BB3F7      2019-11-18 19:49:28 20                          2.1         (temp+battery)
4   3BB3F7      2019-11-18 19:49:27     59.xxx  18.xxx  Exelent
5   12C79E6     2019-11-18 19:49:26 8                           2.9
6   12C79E6     2019-11-18 19:49:25     59.xxx  18.xxx  Good    
7   12C79E6     2019-11-18 19:49:24 8               2.9
8   12C79E6     2019-11-18 19:49:23     59.xxx  18.xxx  Good    

插入时数据库设置的时间戳，id=index

结果显示:deviceid 的最新时间戳、最新温度、最新 lat+lnggps、最新 lgi、所有 deviceid 的最新电池、一行/deviceid

1   12C79E6     2019-11-18 19:49:30 8   59.335xxx   18.033xxx   Good    2.9 
2   3BB3F7      2019-11-18 19:49:28 20  59.335xxx   18.033xxx   Exelent 2.1

Answer 1

这有点难看......您可以使用一系列内联查询:

select 
    d.id,
    d.deviceid,
    (select max(d1.timestamp) from data d1 where d1.deviceid = d.deviceid) timestamp,
    (
        select d1.temp 
        from data d1 
        where 
            d1.deviceid = d.deviceid 
            and d1.temp is not null
        order by d1.timestamp desc
        limit 1
    ) temp,
    (
        select d1.gpslat
        from data d1 
        where 
            d1.deviceid = d.deviceid 
            and d1.gpslat is not null
        order by d1.timestamp desc
        limit 1
    ) gpslat,
    (
        select d1.gpslng 
        from data d1 
        where 
            d1.deviceid = d.deviceid 
            and d1.gpslng is not null
        order by d1.timestamp desc
        limit 1
    ) gpslng,
    (
        select d1.lqi 
        from data d1 
        where 
            d1.deviceid = d.deviceid 
            and d1.lqi is not null
        order by d1.timestamp desc
        limit 1
    ) lqi,
    (
        select d1.battery 
        from data d1 
        where 
            d1.deviceid = d.deviceid 
            and d1.battery is not null
        order by d1.timestamp desc
        limit 1
    ) battery
from (select deviceid, min(id) id from data group by deviceid) d

<强> Demo on DB Fiddle :

id | deviceid | timestamp           | temp | gpslat | gpslng | lqi     | battery-: | :------- | :------------------ | ---: | :----- | :----- | :------ | ------: 1 | 12C79E6  | 2019-11-18 19:49:30 |    8 | 59.xxx | 18.xxx | Good    |     2.9 3 | 3BB3F7   | 2019-11-18 19:49:28 |   20 | 59.xxx | 18.xxx | Exelent |     2.1