MySQL-mysql按指定的字段循环查询

Question

假设数据库中现在有一个字段'a'，字段a中存的值基数为3，例如1,2,3 现在查询来的数据想按照字段a中的值循环查询出来。例如
ID A
10 1
15 2
19 3
12 1
90 2
100 3
...
91 1
24 2
22 3

Answer 1

mysql> select * from ab;
+------+------+
| id | a |
+------+------+
| 34 | 1 |
| 43 | 3 |
| 342 | 2 |
| 23 | 3 |
| 343 | 2 |
| 232 | 1 |
| 4 | 2 |
+------+------+
7 rows in set (0.00 sec)

mysql> set @num=0,@vara=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,a from
-> (select id,a ,@num:=if(@vara=a, @num+1,1) as row_num,@vara:=a as d
-> from ab order by a) as t
-> order by row_num,a;
+------+------+
| id | a |
+------+------+
| 34 | 1 |
| 342 | 2 |
| 43 | 3 |
| 232 | 1 |
| 343 | 2 |
| 23 | 3 |
| 4 | 2 |
+------+------+
7 rows in set (0.00 sec)

注意： 因为数据有7行，a多出一个2，所以被放在了最后。