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)
...
Clientside i take each search by itself and step though them. but on 100.000+ its getting awkward. To be able to use the开发者_StackOverflow中文版 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 does not provide Analytic Functions like ROW_NUMBER
.
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 )
returns
name score_num cre_num
Joe 3 1
Mye 4 2
Zoe 1 3
Abe 2 4
Is this what you want?
精彩评论