Looking for a query solution-CodePudding

I need help with a query solution

table: roomclimate

id	time	deviceId	temperature	humidity
1	2021-12-22 15:49:31	1	23.5	50
2	2021-12-22 15:49:31	2	10.5	50
3	2021-12-22 15:59:31	1	23.5	56
4	2021-12-22 15:59:31	2	10.7	57

and now i want to group the values like this

time	dev_1_temp	dev_1_humi	dev_2_temp	dev_2_humi
2021-12-22 15:49:31	23.5	50	10.5	50
2021-12-22 15:59:31	23.5	56	10.7	57

CodePudding user response：

So, this is working.. but is there an better Solution?

SELECT
    `time`,

    MAX(IF(`deviceId`=1,ROUND(`temperature`,1),NULL)) AS dev_1_temp,
    MAX(IF(`deviceId`=1,`humidity`,NULL)) AS dev_1_humi,
    MAX(IF(`deviceId`=2,ROUND(`temperature`,1),NULL)) AS dev_2_temp,
    MAX(IF(`deviceId`=2,`humidity`,NULL)) AS dev_2_humi
 

FROM tbl_klima
GROUP BY `time` DESC

CodePudding user response：

Try this:

SELECT
    base.time,
    dev1.temperature, dev1.humidity,
    dev2.temperature, dev2.humidity
FROM roomclimate AS base
LEFT JOIN roomclimate AS dev1
    ON dev1.time = base.time AND dev1.deviceId = 1
LEFT JOIN roomclimate AS dev2
    ON dev2.time = base.time AND dev2.deviceId = 2
ORDER BY base.time DESC

I hope that you have an index on time.