mysql - 按多列的最高平均排名排序

Question

我遇到了一个棘手的情况，我需要按三列中的最高位置对一个表中的行进行排序。

例如，这里是一个基本表；

ID  Stat1   Stat2   Stat3
--------------------------
1   400     100     200
2   200     200     100
3   100     400     400
4   300     300     300

理想情况下，每一行将按其在每一列中的平均位置排序，并按最低的升序排序。这是理想情况下返回的内容:

ID  Average
------------
3   2
4   2
2   2.3333333333333
1   2.6666666666667

第一个 ID 的位置最低，因为对于第一、第二和第三个统计数据，它排名 1、4 和 3，这是 2.6666666666667 的平均值。

Answer 1

这个怎么样？

SELECT id, (rank_by_stat1 + rank_by_stat2 + rank_by_stat3) / 3 AS avg
FROM (
    SELECT id, @rank_by_stat1 := @rank_by_stat1 + 1 AS rank_by_stat1
    FROM test, (SELECT @rank_by_stat1 := 0) init
    ORDER BY stat1 DESC
) stat1 INNER JOIN (
    SELECT id, @rank_by_stat2 := @rank_by_stat2 + 1 AS rank_by_stat2
    FROM test, (SELECT @rank_by_stat2 := 0) init
    ORDER BY stat2 DESC
) stat2 USING(id) INNER JOIN (
    SELECT id, @rank_by_stat3 := @rank_by_stat3 + 1 AS rank_by_stat3
    FROM test, (SELECT @rank_by_stat3 := 0) init
    ORDER BY stat3 DESC
) stat3 USING(id)
ORDER BY avg;
+------+--------------------+
| id   | avg                |
+------+--------------------+
|    3 |                  2 |
|    4 |                  2 |
|    1 | 2.6666666666666665 |
|    2 | 3.3333333333333335 |
+------+--------------------+
4 rows in set (0.00 sec)

工作原理

非常简单。我已将变量名称修改为不言自明。查看最内部查询之一的输出会很有帮助:

SELECT id, @rank_by_stat1 := @rank_by_stat1 + 1 AS rank_by_stat1, stat1
FROM test, (SELECT @rank_by_stat1 := 0) init
ORDER BY stat1 DESC
+------+---------------+-------+
| id   | rank_by_stat1 | stat1 |
+------+---------------+-------+
|    1 |             1 |   400 |
|    4 |             2 |   300 |
|    2 |             3 |   200 |
|    3 |             4 |   100 |
+------+---------------+-------+