mysql组内排序取前两个字段_mysql分组，然后组内排序，最后取每组前2条数据
1.首先根据城市分组，然后查询出点赞数为前2的数据SELECTrelease_city,SUBSTRING_INDEX(group_concat(dazzle_idORDER BYweek_praise_num DESC),',',2)FROMsc_dazzle_dazzle_infoWHEREdazzle_state = 1AND re_status = 1GROUP BYrelease_cit
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,的格式去匹配，既可查询出满足要求的数据
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献9条内容
所有评论(0)