mysql - MySQL get rows starting with specific id after sort / order by

Question

I got this table:

id | score
1  | 1
2  | 4
3  | 4
4  | 3
5  | 2
6  | 2
7  | 1
8  | 4
9  | 2
10 | 3

I need to order it by score desc:

id | score
2  | 4
3  | 4
8  | 4
4  | 3
10 | 3
5  | 2
6  | 2
9  | 2
1  | 1
7  | 1

and get first 3 rows which starts with id 6

So the result should be:

6  | 2
9  | 2
1  | 1

Is this possible? Thanks in advance

标签： mysqlsqldatabasesql-order-by

Answer 1

我会用累积的方法来解决这个问题sum()（在 MySQL 8.0 中可用）：

select 
    id, 
    score
from mytable
order by 
    sum(id = 6) over(order by score desc, id) desc, 
    score desc,
    id
limit 3

订单按要求的sum()方向记录；一旦id = 6满足记录，总和就会取值1。它允许将这些记录放在首位。剩下的只是添加额外的排序标准并限制结果的数量。

DB Fiddle 上的演示：

| id  | score |
| --- | ----- |
| 6   | 2     |
| 9   | 2     |
| 1   | 1     |

---

在早期版本的 mysql 中，您可以使用用户变量来模拟窗口总和，如下所示：

select 
    id, 
    score
from 
  (select @sm := 0) s
  cross join (select id, score from mytable order by score desc, id) t
order by 
    case when id = 6 then @sm := @sm + 1 end desc, 
    score desc,
    id
limit 3

DB Fiddle 上的演示：相同的结果