Mysql分组之后添加序列号

1，业务场景描述

统计学校学生性别比例分布最高的前5个城市

2，表结构

城市表：
student_city:city_id,city_name
学生信息表：
student_info:stu_id,stu_name,stu_gender,city_id

3，SQL实现

SELECT (@i := @i+1) AS '排名', 
si.stu_id AS 'ID', 
si.stu_name AS '姓名', 
CASE si.stu_gender 
WHEN 0 THEN '男' 
WHEN 1 THEN '女' 
END AS '性别' 
FROM student_info si ,(SELECT @i := 0) s 
ORDER BY si.stu_id DESC 
LIMIT 5

排名	ID	姓名	性别
1	9	孔雀公主	女
2	8	蝎子精	女
3	7	牛魔王	男
4	6	铁扇公主	女
5	5	白骨精	女

表1-单表查询操作

SELECT (@i := @i+1) AS '排名', 
sc.city_name AS '城市', 
COUNT(si.stu_gender) AS '总数', 
si.city_id AS '城市ID' 
FROM (SELECT @i := 0) s, student_info si 
LEFT JOIN student_city sc ON sc.city_id = si.city_id 
GROUP BY sc.city_name 
ORDER BY COUNT(si.stu_gender) DESC 
LIMIT 5

排名	城市	总数	城市ID
2	上海	2	2
3	广州	2	3
6	商丘	2	6
1	北京	1	1
4	深圳	1	4

表2-分组查询操作

单表操作是没有任何问题的，可是加了GROUP BY分组之后发现序列号是乱序

SELECT (@i := @i+1) AS '排名',
cityName AS '城市',
total AS '总数',
city_id AS '城市ID' 
FROM ( 
SELECT sc.city_name AS cityName, 
COUNT(si.stu_gender) AS total, 
si.city_id 
FROM (SELECT @i := 0) s, student_info si 
LEFT JOIN student_city sc ON sc.city_id = si.city_id 
GROUP BY sc.city_name 
ORDER BY COUNT(si.stu_gender) DESC 
LIMIT 5 
) d

排名	城市	总数	城市ID
1	上海	2	2
2	广州	2	3
3	商丘	2	6
4	北京	1	1
5	深圳	1	4

表3-分组再查询