PIVOTing既不容易（也不是很好）。我更喜欢使用
CASE：
SELECt d.data_timestamp , SUM( CASE WHEN data_id = 1 THEN data_value ELSE 0 END ) AS 'input_1' , SUM( CASE WHEN data_id = 2 THEN data_value ELSE 0 END ) AS 'input_2' ... , SUM( CASE WHEN data_id = 20 THEN data_value ELSE 0 END ) AS 'input_20'FROM data GROUP BY data_timestampORDER BY data_timestamp ASC
但也
IF可以在MySQL中使用：
SELECt d.data_timestamp , SUM( IF(data_id = 1, data_value, 0) ) AS 'input_1' , SUM( IF(data_id = 2, data_value, 0) ) AS 'input_2' ... , SUM( IF(data_id = 20, data_value, 0) ) AS 'input_20'FROM data GROUP BY data_timestampORDER BY data_timestamp ASC
另外，您可以使用20级
JOIN：
SELECt d.data_timestamp , d01.data_value AS 'input_1' , d02.data_value AS 'input_2' ... , d20.data_value AS 'input_20'FROM ( SELECt DISTINCT d.data_timestamp FROM data ) AS d LEFT JOIN data AS d01 ON d01.data_timestamp = d.data_timestamp AND d01.data_id = 1 LEFT JOIN data AS d02 ON d02.data_timestamp = d.data_timestamp AND d02.data_id = 2 ... --- 20 JOINs LEFT JOIN data AS d20 ON d20.data_timestamp = d.data_timestamp AND d20.data_id = 20ORDER BY d.data_timestamp ASC
