在一个查询中排序混合混合mysql搜索结果？

Question

这个问题很容易在客户端解决。但是为了提高性能，我想直接对数据库执行此操作。

LIST a
+------+-------+-------+
| name | score | cre   |
+------+-------+-------+
| Abe  | 3     | 1     |
| Zoe  | 5     | 2     |
| Mye  | 1     | 3     |
| Joe  | 3     | 4     |

我想在没有重复的情况下检索连接的混合结果。

Zoe (1st highest score)
Joe (1st last submitted)
Abe (2nd highest score)
Mye (2nd last submitted)
...

在客户端，我自己处理每个搜索，并逐个遍历它们。但在100.000+上，它变得越来越笨拙。能够使用LIMIT函数会让事情变得轻松很多！

SELECT name FROM a ORDER BY score DESC, cre DESC;
SELECT name FROM a ORDER BY cre DESC, score DESC;

Answer 1

MySQL不像ROW_NUMBER那样提供分析功能。

实现这一点的一种方法是将两个具有不同Order By的查询连接在一起：

Select s.name, s.score_num, c.cre_num
From
(
    Select name, @score_num := @score_num + 1 As score_num
    From your_table, ( SELECT @score_num := 0 ) v
    Order By score Desc
) s
Join
(
    Select name, @cre_num := @cre_num + 1 As cre_num
    From your_table, ( SELECT @cre_num := 0 ) v
    Order By cre Desc
) c
On ( c.name = s.name )

返回

name  score_num  cre_num
Joe           3        1
Mye           4        2
Zoe           1        3
Abe           2        4

这是你想要的吗？