mysql组内排序等数据库操作

--- 组内排序
SELECT substring_index(group_concat(g.id order by case g.status when 3 then 0 when 2 then 1 when 5 then 4 else g.status end),',',1) topId  FROM gas_groupon_plan g  group by g.product_id

-- 关联查询 重写bean 的get 方法 达到一对多查询的目的
select c.*, usc.user_collages user_collages_str
from  collage c
left join (
select concat('[',group_concat('{\"userId\":',IFNULL(uc.user_id,''),',\"userName\":\"',IFNULL(u.user_name,''),'\\"headImgUrl\":\"',IFNULL(REPLACE(u.head_img_url,'https://okche.oss-cn-shanghai.aliyuncs.com/tyb/images/tyz/',''),''),'\"}'),']') user_collages, uc.collage_id
from user_collage uc  left join user u on u.id = uc.user_id where uc.collage_id=#{id,jdbcType=INTEGER} and uc.data_status =1 group by uc.collage_id
) usc on usc.collage_id = c.id
where uc.type=1 and c.id= 111

转载于:https://my.oschina.net/u/1470043/blog/2049509