mysql - 为选择状态 1 的第一条记录和状态 2 的第二条记录编写查询，依此类推

Question

我想编写一个查询来选择状态 1 的第一条记录和状态 2 的第二条记录等等

我有这样的 table

+----+--------+--------+
| id |   name | status |
+----+--------+--------+
|  1 |   ABC  |      1 |
|  2 |   PQR  |      1 |
|  3 |   qqq  |      2 |
|  4 |   www  |      1 |
|  5 |   eee  |      2 |
|  6 |   rrr  |      2 |
|  7 |   ttt  |      2 |
+----+--------+--------+

我需要如下输出

+----+-------+---------+
| id |  name |  status |
+----+-------+---------+
|  1 |   ABC |       1 |
|  3 |   qqq |       2 |
|  2 |   PQR |       1 |
|  5 |   eee |       2 |
|  4 |   www |       1 |
|  6 |   rrr |       2 |
|  7 |   ttt |       2 |
+----+-------+---------+

记录的顺序应该是这样的

- 1st record with status 1
- 2nd record with status 2
- 3rd record with status 1
- 4th record with status 2
- 5th record with status 1
- 6th record with status 2

• 7th record with status 2 -> if we not find any record with status 1 then select records with status 2 & vice versa

我已经通过编码得到了这个输出，但是有可能通过 MySQL 查询得到这样的输出

Answer 1

你可以为此使用变量:

SELECT id, name, status
FROM (
   SELECT id, name, status,
       @grp := IF(@status = status, @grp + 1, 
                  IF(@status := status, 1, 1)) AS grp
   FROM mytable
   CROSS JOIN (SELECT @grp := 0, @status := 0) AS vars
   ORDER BY status, name) AS t
ORDER BY grp, status, name   

所用算法的关键是变量@grp:它本质上模拟了ROW_NUMBER() OVER (PARTITION BY status)。使用@grp，我们可以轻松实现order by alternating status 效果。

Demo here