mysql - 合并 MySQL 中同一时间戳上具有空值的行

Question

我有一个查询返回我想要的所有数据，但由于 case 语句的执行方式，有很多额外的空值。我试图摆脱所有空值，每个时间戳只有一行数据。这是查询:

SELECT
    FROM_UNIXTIME(t_stamp/1000, '%b %D, %Y %h:%i %p') as t_stamp,
    (case when t.id = 460 then s.floatvalue end) as ait_3022_btu_dry,
    (case when t.id = 460 then s.dataintegrity end) as ait_3022_btu_dry_qual,
    (case when t.id = 481 then s.floatvalue end) as ait_3022_ch4,
    (case when t.id = 481 then s.dataintegrity end) as ait_3022_ch4_qual,
    (case when t.id = 622 then s.floatvalue end) as fqe_3004_cd_lr,
    (case when t.id = 622 then s.dataintegrity end) as fqe_3004_cd_lr_qual,
    (case when t.id = 641 then s.floatvalue end) as at_3005,
    (case when t.id = 641 then s.dataintegrity end) as at_3005_qual,
    (case when t.id = 647 then s.floatvalue end) as ft_3004_scfm,
    (case when t.id = 647 then s.dataintegrity end) as ft_3004_scfm_qual,
    (case when t.id = 934 then s.floatvalue end) as ft_3005_scfm,
    (case when t.id = 934 then s.dataintegrity end) as ft_3005_scfm_qual
FROM sqlt_data_1_2019_10 s
    inner join sqlth_te t
    on s.tagid = t.id
WHERE
    (s.floatvalue AND s.dataintegrity) IS NOT NULL
    AND 
    (t_stamp >= 1570597200000 and t_stamp <= 1570629600000)
    AND 
    (tagid = 934
    or tagid = 647
    or tagid = 622
    or tagid = 641
    or tagid = 460
    or tagid = 481)
ORDER BY t_stamp ASC

这是它返回的内容:

我只需为每个时间戳返回一行

Answer 1

您只需要打开聚合:

SELECT
    FROM_UNIXTIME(t_stamp/1000, '%b %D, %Y %h:%i %p') as t_stamp,
    MAX(case when t.id = 460 then s.floatvalue end) as ait_3022_btu_dry,
    MAX(case when t.id = 460 then s.dataintegrity end) as ait_3022_btu_dry_qual,
    MAX(case when t.id = 481 then s.floatvalue end) as ait_3022_ch4,
    MAX(case when t.id = 481 then s.dataintegrity end) as ait_3022_ch4_qual,
    MAX(case when t.id = 622 then s.floatvalue end) as fqe_3004_cd_lr,
    MAX(case when t.id = 622 then s.dataintegrity end) as fqe_3004_cd_lr_qual,
    MAX(case when t.id = 641 then s.floatvalue end) as at_3005,
    MAX(case when t.id = 641 then s.dataintegrity end) as at_3005_qual,
    MAX(case when t.id = 647 then s.floatvalue end) as ft_3004_scfm,
    MAX(case when t.id = 647 then s.dataintegrity end) as ft_3004_scfm_qual,
    MAX(case when t.id = 934 then s.floatvalue end) as ft_3005_scfm,
    MAX(case when t.id = 934 then s.dataintegrity end) as ft_3005_scfm_qual
FROM sqlt_data_1_2019_10 s
    inner join sqlth_te t
    on s.tagid = t.id
WHERE
    (s.floatvalue AND s.dataintegrity) IS NOT NULL
    AND 
    (t_stamp >= 1570597200000 and t_stamp <= 1570629600000)
    AND 
    (tagid = 934
    or tagid = 647
    or tagid = 622
    or tagid = 641
    or tagid = 460
    or tagid = 481)
GROUP BY FROM_UNIXTIME(t_stamp/1000, '%b %D, %Y %h:%i %p')
ORDER BY t_stamp ASC

另外，正如 Gordon Linoff 所评论的，请注意 WHERE 子句中 tagid 的条件写得更好:

tag_id IN (934, 647, 622, 641, 460, 481)

编辑:

由于我提出了这个主题，where 子句应该如下所示:

WHERE s.floatvalue IS NOT NULL AND
      s.dataintegrity IS NOT NULL AND
      t_stamp >= 1570597200000 AND
      t_stamp <= 1570629600000 AND
      tagid IN (934, 647, 622, 641, 460, 481)