mysql 分组查询以及显示各个分组所有的温度和时间要用GROUP_CONCAT

select GROUP_CONCAT(a.TEMPERATURE)TEMPERATURE,GROUP_CONCAT(DATE_FORMAT(a.OCCUR_TIME,'%H:%i'))OCCUR_TIME,a.THERMO_TYPE,a.THERMO_ID FROM
(select THERMO_RECORD_ID,TEMPERATURE,OCCUR_TIME,th.THERMO_TYPE,th.THERMO_ID from temmointor t
LEFT JOIN thermo th ON th.ID = t.THERMO_RECORD_ID
where t.FARM_ID = 3853 and t.BUILD_NUM = 1 and t.OCCUR_DATE = DATE_FORMAT('2016-12-19','%Y-%m-%d')
and t.PRODUCE_ID = (select id from produce p where p.ProduceCode=1002)
ORDER BY t.OCCUR_TIME desc)a
GROUP BY a.THERMO_RECORD_ID
ORDER BY a.OCCUR_TIME desc

;

mysql默认的GROUP_CONCAT的长度是1024

可以用下面的语句显示：

SHOW VARIABLES LIKE "group_concat_max_len";
 

修改GROUP_CONCAT的长度

SET GLOBAL group_concat_max_len = 102400;

SET SESSION group_concat_max_len = 102400;