我正在使用下面的代码来获取人们注册并输入数据的团队的总距离,然后为团队分配职位。
SELECT @curRow := @curRow + 1 AS position, ROUND(SUM(d.dist_activity_duration
* CASE
WHEN d.dist_is_distance = 0 THEN s.activity_steps / 2000
WHEN d.dist_is_distance = 1 THEN 1
END)
,2) AS miles, t.team_name AS team_name
FROM distance d
JOIN (SELECT @curRow := 0) r
JOIN activities a
ON a.id = d.dist_activity_id
JOIN steps s
ON s.id = a.steps_id
JOIN members AS m
ON d.member_id = m.id
JOIN teams AS t
ON t.id = m.member_team_id
GROUP BY team_name
ORDER BY miles DESC
上面的代码输出以下结果
position miles team_name
2 134.05 team 1
1 78.00 team 2
我希望将位置1分配给英里数最高的团队,将位置2分配给第二高的团队……等等。
在MySQL 8+中,您只需使用row_number()
:
SELECT ROW_NUMBER() OVER (ORDER BY miles DESC) AS position, t.*
FROM (SELECT ROUND(SUM(d.dist_activity_duration *
CASE WHEN d.dist_is_distance = 0 THEN s.activity_steps / 2000
WHEN d.dist_is_distance = 1 THEN 1
END), 2) AS miles, t.team_name AS team_name
FROM distance d JOIN
activities a
ON a.id = d.dist_activity_id JOIN
steps s
ON s.id = a.steps_id JOIN
members m
ON d.member_id = m.id JOIN
teams t
ON t.id = m.member_team_id
GROUP BY team_name
) t
ORDER BY miles DESC;
MySQL的早期版本支持变量,但不能与GROUP BY
and 很好地配合使用ORDER BY
。解决方案是一个子查询(如上所述):
SELECT (@rn := @rn + 1) AS position,
FROM (SELECT ROUND(SUM(d.dist_activity_duration *
CASE WHEN d.dist_is_distance = 0 THEN s.activity_steps / 2000
WHEN d.dist_is_distance = 1 THEN 1
END), 2) AS miles, t.team_name AS team_name
FROM distance d JOIN
activities a
ON a.id = d.dist_activity_id JOIN
steps s
ON s.id = a.steps_id JOIN
members m
ON d.member_id = m.id JOIN
teams t
ON t.id = m.member_team_id
GROUP BY team_name
ORDER BY miles DESC
) t CROSS JOIN
(SELECT @rn := 0) params;
刚刚检查,我们的服务器正在使用版本:5.6。有替代品吗
row_number()
查询返回
position 2 3
@ user1809642。。。起始值应该
0
不会1
。在
0
返回值处开始值1 1
与从零开始值无关,但未返回其他列。