mysql - 合并行但维护单独的列

我不确定如何将多个表中的数据聚合到一个表中-任何人都可以帮忙吗？

我有大量的“历史数据”表，每个表有2列：时间戳记（bigint，unix毫秒）和值（双精度）。

我正在尝试从多个表中收集数据，并为每个表每天计算最大值。

到目前为止，我的查询如下：

SELECT DATE(FROM_UNIXTIME(Timestamp/1000)) AS Date,
CASE WHEN Source = 'Meter 1' THEN MAX(Value) ELSE '' END AS 'Meter 1',
CASE WHEN Source = 'Meter 2' THEN MAX(Value) ELSE '' END AS 'Meter 2',
CASE WHEN Source = 'Meter 3' THEN MAX(Value) ELSE '' END AS 'Meter 3'
FROM
(
    SELECT  'Meter 1' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
    FROM    <table name 1>
    UNION ALL
    SELECT  'Meter 2' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
    FROM    <table name 2>
    UNION ALL
    SELECT  'Meter 3' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
    FROM    <table name 3>
) AS data
GROUP BY Source, DATE(FROM_UNIXTIME(Timestamp/1000));

如果仅将GROUP BY DATE（FROM_UNIXTIME（Timestamp / 1000））分组，则只能看到仪表1的结果。通过按Source分组，也可以看到所有结果，但日期不会汇总，例如：

Date        Meter 1     Meter 2     Meter 3
2019-12-11  26
2019-12-12  27
2019-12-13  28
2019-12-14  29
2019-12-15  30
2019-12-16  31
2019-12-17  31
2019-12-11              18
2019-12-12              18
2019-12-13              18
2019-12-14              22
2019-12-15              22
2019-12-16              22
2019-12-17              22
2019-12-11                          3747
2019-12-12                          3784
2019-12-13                          3819
2019-12-14                          3847
2019-12-15                          3875
2019-12-16                          3908
2019-12-17                          918

我已经阅读了一些相关的问题，但无法弄清楚。我确定我缺少一些简单的东西。

首先十分感谢。

最佳答案

您尚未提供测试数据，但是让我们尝试一下

SELECT DATE(FROM_UNIXTIME(Timestamp/1000)) AS Date,
MAX(if(Source = 'Meter 1', Value, 0)) AS 'Meter 1',
MAX(if(Source = 'Meter 2', Value, 0)) AS 'Meter 2',
MAX(if(Source = 'Meter 3', Value, 0)) AS 'Meter 3'
FROM
(
    SELECT  'Meter 1' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
    FROM    <table name 1>
    UNION ALL
    SELECT  'Meter 2' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
    FROM    <table name 2>
    UNION ALL
    SELECT  'Meter 3' AS Source, TIMESTAMP AS Timestamp, VALUE AS Value
    FROM    <table name 3>
) AS data
GROUP BY DATE(FROM_UNIXTIME(Timestamp/1000));

关于mysql - 合并行但维护单独的列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59394323/