mysql组内排序取前两个字段_mysql分组，然后组内排序，最后取每组前2条数据

1.首先根据城市分组，然后查询出点赞数为前2的数据

SELECT

release_city,

SUBSTRING_INDEX(

group_concat(

dazzle_id

ORDER BY

week_praise_num DESC

),

',',

2

)

FROM

sc_dazzle_dazzle_info

WHERE

dazzle_state = 1

AND re_status = 1

GROUP BY

release_city

2.合并这个数据的主键字段为一行

SELECT

group_concat(dazzle_ids)

FROM

(

SELECT

SUBSTRING_INDEX(

group_concat(

dazzle_id

ORDER BY

week_praise_num DESC

),

',',

2

) AS dazzle_ids

FROM

sc_dazzle_dazzle_info

WHERE

dazzle_state = 1

AND re_status = 1

GROUP BY

release_city

) a在这一步获取到的主键数据格式为：

1182,1181,1223,1209,1253,1246,1266,1265,104,103,107,105,1160,1159,1270,1269

3.根据这一行的主键字段查询数据

SELECT

a.release_city,

a.dazzle_id,

a.user_id,

b.usercode,

b.phone,

b.username,

a.introduce,

a.week_praise_num

FROM

sc_dazzle_dazzle_info a

LEFT JOIN sc_user.sc_sys_user b ON a.user_id = b.id

WHERE

LOCATE(

CONCAT(",", dazzle_id, ","),

(

SELECT

CONCAT(",", group_concat(dazzle_ids), ",")

FROM

(

SELECT

SUBSTRING_INDEX(

group_concat(

dazzle_id

ORDER BY

week_praise_num DESC

),

',',

2

) AS dazzle_ids

FROM

sc_dazzle_dazzle_info

WHERE

dazzle_state = 1

AND re_status = 1

GROUP BY

release_city

) tem_a

)

) > 0第散步中将数据变成了

，

1182,1181,1223,1209,1253,1246,1266,1265,104,103,107,105,1160,1159,1270,1269

,

然后然后再将主键变成,1181,的格式去匹配，既可查询出满足要求的数据