select c.`name`,b.movie_names,time2 from (
select cinema_id,substring_index(group_concat(movie_id order by time1 desc separator '----'),'----',3) as movie_id,substring_index(group_concat(movie_name order by time1 desc separator '----'),'----',3) as movie_names,substring_index(group_concat(time1 order by time1 desc separator '----'),'----',3) as time2 from (
select cinema_id,movie_id,movie_name,substring_index(group_concat(DATE_FORMAT(start_time,'%Y-%m-%d %H:%i') order by start_time desc separator '----'),'----',1) as time1 from table_film_feature where city_code='110100' and movie_id in (select id from table_film_movie where publish_time > now()) group by cinema_id,movie_id
)a group by cinema_id)b ,table_film_cinema c where b.cinema_id=c.id
多字段分组后按时间倒序,截取前几个数据再拼接,并将时间格式化
了解group_concat的用法