将MySQL查询合并到表中

Question

我有一个尽可能高效的表，允许表中的每个数据点只有一个条目，并将条目类型链接到另一个表，将位置链接到第三个表。

这实际上意味着在传感器表中，我定义了传感器和sensorID的位置，在测试表中，我定义了可以运行的测试、testID、单位、测试的名称以及上限和下限，而在最终的表(数据表)中，我引用了entryID、当前日期时间、sensorID、testID和测试值。

这意味着对于每个传感器和测试，该表有许多单独的记录，但这些记录可能同时发生，例如，一个传感器可以同时输出4个测试。

我使用如下所示的SELECT来获取基本信息；

SELECT raw_data.entry_date, sensorid.desk as location, tests.testname as test, raw_data.test_value as testresult 
FROM raw_data 
INNER JOIN sensorID ON raw_data.sensorID = SensorID.Sensor 
INNER JOIN tests ON raw_data.test_id = tests.testid 
ORDER BY entry_date ASC

这可以很好地获取每个条目，或者通过测试进行过滤，但如果我试图按时间和日期对所有结果进行分组，则效果很糟糕。有没有一种方法可以使动态MySQL结果更多地呈现为基于列的系统，例如

日期|传感器|测试A来自(tests.testname)|测试B ... |测试C ... |测试D ...

这样的想法是，在测试A的列中，它将显示测试A的结果，依此类推。

我会进行过滤，所以不会查询整个数据库，而是查询特定的传感器/站点等，但主要的事情是如何动态地查询，这样我就不需要每次都更改所有的列名和所有内容了。

最终，我希望能够将其提供给JSON，但我需要按日期和位置进行分组，并跨列显示测试名称，行值是每个测试名称的test_value。

非常感谢！

Answer 1

我认为您只需要一个基本的pivot查询：

SELECT
    r.entry_date,
    s.desk AS location,
    MAX(CASE WHEN t.testname = 'A' THEN r.test_value END) AS test_A,
    MAX(CASE WHEN t.testname = 'B' THEN r.test_value END) AS test_B,
    MAX(CASE WHEN t.testname = 'C' THEN r.test_value END) AS test_C
FROM raw_data r
INNER JOIN sensorID s
    ON r.sensorID = s.Sensor 
INNER JOIN tests t
    ON r.test_id = t.testid 
GROUP BY
    r.entry_date,
    s.desk
ORDER BY
    r.entry_date;