我有一个体育数据库,我想在其中按自定义字段("评级"(对数据进行排序,并使用行号更新字段("排名"(。
我尝试了以下代码按我的自定义字段"评级"对数据进行排序。当我按普通字段对其进行排序时,它有效,但不适用于自定义/计算字段。排序完成后,我希望它使用行号更新字段"排名"。
即具有最高"评级"的战斗机应将值"1"作为"排名"。
SELECT id,lastname, wins, Round(((avg(indrating)*13) + (avg(Fightrating)*5) * 20) / 2,2) as Rating,
ROW_NUMBER() OVER (ORDER BY 'Rating' DESC) AS num
from fighters
JOIN fights ON fights.fighter1 = fighters.id
GROUP BY id
上面的代码没有准确地对评级进行排序。它按行号排序,但最高评级未评级为 #1。这似乎有点随机。
SQL Fiddle:http://sqlfiddle.com/#!9/aa1fca/1(此示例排序正确,但我希望它按行号更新"排名"列 - 这意味着评分最高的战斗机(通过"评级"(在排名列中获得"1",第二高的战斗机在排名列中获得"2"等(。
此外,我希望能够在fighters表中添加WHERE子句(例如,fighters.organisation = 'UFC'(。
首先,让我们修复您的查询,使其在MySQL <8.0上运行。这需要在子查询中进行计算和排序,然后使用变量来计算排名:
select
id,
rating,
@rnk := @rnk + 1 ranking
from
(select @rnk := 0) r
cross join (
select
fighter1 id,
round(((avg(indrating)*13) + (avg(fightrating)*5) * 20) / 2,2) as rating
from fights
group by fighter1
order by rating desc
) x
现在我们使用update ... join ... set ...
语法来更新fighters
表:
update fighters f
inner join (
select
id,
rating,
@rnk := @rnk + 1 ranking
from
(select @rnk := 0) r
cross join (
select
fighter1 id,
round(((avg(indrating)*13) + (avg(fightrating)*5) * 20) / 2,2) as rating
from fights
group by fighter1
order by rating desc
) x
) y on y.id = f.id
set f.ranking = y.ranking;
根据您在评论中提供的小提琴在MySQL 5.6 小提琴中进行演示。
选择查询返回:
| id | rating | ranking |
| --- | ------ | ------- |
| 3 | 219.5 | 1 |
| 4 | 213 | 2 |
| 1 | 169.5 | 3 |
| 2 | 156.5 | 4 |
这是update
后fighters
表的内容:
| id | lastname | ranking |
| --- | ---------- | ------- |
| 1 | Gustafsson | 3 |
| 2 | Cyborg | 4 |
| 3 | Jones | 1 |
| 4 | Sonnen | 2 |