1. SELECT DATE_FORMAT(rd_sensor_data.create_time, '%Y-%m-%d %H:00:00') trigger_day,
    2. dm_device.id         as  device_id, 
    3. any_value(rd_sensor_data.temperature)  as  temperature, 
    4. any_value(rd_sensor_data.humidity)     as  humidity, a
    5. ny_value(rd_sensor_data.conductivity) as  conductivity, 
    6. any_value(rd_sensor_data.ph)           as  ph, 
    7. any_value(rd_sensor_data.nitrogen)     as  nitrogen, 
    8. any_value(rd_sensor_data.potassium)    as  potassium, 
    9. any_value(rd_sensor_data.phosphor)    as  phosphor 
    10. FROM `dm_device` left join rd_sensor_data on dm_device.id = rd_sensor_data.device_id 
    11. WHERE dm_device.type = 1 AND rd_sensor_data.create_time >= (NOW() - interval 24 hour) AND dm_device.id = '0117370018' 
    12. GROUP BY dm_device.id, DATE_FORMAT(rd_sensor_data.create_time, '%Y-%m-%d %H:00:00') ORDER BY DATE_FORMAT(rd_sensor_data.create_time, '%Y-%m-%d %H:00:00') desc LIMIT 24
    1. SELECT
    2. t1. HOUR HOUR,
    3. t2.device_id,t2.humidity
    4. FROM
    5. (
    7.   SELECT
    8.   DATE_FORMAT(
    9.     @cdate := DATE_ADD(@cdate, INTERVAL - 1 HOUR),
    10.     '%y-%m-%d %H'
    11.   ) HOUR
    12.   FROM
    13.   (
    14.     SELECT
    15.     @cdate := DATE_ADD(
    16.       DATE_FORMAT(NOW(), '%y-%m-%d %H'),
    17.       INTERVAL + 1 HOUR
    18.     )
    19.     FROM
    20.     rd_sensor_data  #记录⼤于等于24条的任意⼀张表
    21.   ) t0
    22.   LIMIT 24
    24. ) t1
    25. LEFT JOIN (
    27.   SELECT
    28.   DATE_FORMAT(create_time, '%y-%m-%d %H') HOUR,ANY_VALUE(`device_id`) as `device_id`,ANY_VALUE(`humidity`) as `humidity`
    29.   FROM
    30.   rd_sensor_data  #真正要查的记录表
    31.   WHERE
    32.   create_time >= (NOW() - INTERVAL 24 HOUR)
    34. ) t2 ON t1. HOUR = t2. HOUR
    35. GROUP BY
    36. t1. HOUR
    37. ORDER BY
    38. t1. HOUR DESC
    41.    SELECT `name`,`lat`,`lng`,`state` FROM `dm_device`