mysql - SQL 查询按行号排序和更新(SQLFiddle 示例)

Question

我有一个体育数据库，我想按自定义字段(“评级”)对数据进行排序，并使用行号更新字段(“排名”)。

我已尝试使用以下代码按自定义字段“评级”对数据进行排序。当我按普通字段排序但不使用自定义/计算字段排序时，它会起作用。排序完成后，我希望它用行号更新“排名”字段。

即“评级”最高的战斗机的“排名”应为“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” ' 在排名列等中)。

此外，我还希望能够在战斗机表中添加 WHERE 子句(例如，其中fighters.organization = 'UFC')。

Answer 1

首先，让我们修复您的查询，使其在 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;

<强> Demo in a MySQL 5.6 fiddle 基于您在评论中提供的 fiddle 。

选择查询返回:

| id  | rating | ranking |
| --- | ------ | ------- |
| 3   | 219.5  | 1       |
| 4   | 213    | 2       |
| 1   | 169.5  | 3       |
| 2   | 156.5  | 4       |

以下是更新后fighters表的内容:

| id  | lastname   | ranking |
| --- | ---------- | ------- |
| 1   | Gustafsson | 3       |
| 2   | Cyborg     | 4       |
| 3   | Jones      | 1       |
| 4   | Sonnen     | 2       |