mysql - 根据 SUM 为行分配位置值
问题描述
我正在使用下面的代码来获取人们注册并输入数据的团队的总距离,然后为团队分配一个位置。
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
和一起使用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;
推荐阅读
- sql - 在 QGIS 中为属性搜索表找到正确的 SQL 表达式
- go - How to build extensions to existing packages idiomatically
- scala - polymorphic dispatch from object-oriented programming with pattern matching
- r - 如何在 R 中舍入数据帧的数字以保留总和?
- python - 如何在 python 中修复“IndexError:单个位置索引器越界”?
- mysql - MySQL get rows starting with specific id after sort / order by
- lua - 如何修复 eof 附近的最终预期错误
- javascript - "
" 不是从 "./path" 导出的 - c - 在单个链表中插入字符串
- swift - 锻炼崩溃后未调用 WatchOS6 handleActiveWorkoutRecovery