在一个查询中订购混合混合 mysql 搜索结果? [英] Order hybrid mixed mysql search result in one query?

问题描述

这个问题很容易在客户端解决.但是为了性能我想直接对数据库做.

This problem is easy fixed clientside. But for performance I want to do it directly to the database.

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

想要检索没有重复的合并混合结果.

Want to retrieve a joined hybrid result without duplications.

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

客户端我自己进行每个搜索并逐步完成它们.但在 100.000+ 上它变得尴尬.能够使用 LIMIT 函数会让事情变得轻松很多！

Clientside i take each search by itself and step though them. but on 100.000+ its getting awkward. To be able to use the LIMIT function would ease things up a lot!

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

推荐答案

MySQL 不提供 ROW_NUMBER 之类的分析函数.

MySQL does not provide Analytic Functions like ROW_NUMBER.

实现此目的的一种方法是将两个具有不同 Order By 的查询连接在一起:

One way to implement this would be two queries with different Order By, joined together:

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

这是你想要的吗?

这篇关于在一个查询中订购混合混合 mysql 搜索结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！