mysql根据指定字段排序_mysql如何根据某个字段的值进行循环排序？

有意思的问题。试着做个简化版供参考

mysql> select * from t limit 10;

+-----+--------+

| id | c |

+-----+--------+

| 521 | group2 |

| 522 | group2 |

| 523 | group3 |

| 524 | group2 |

| 525 | group3 |

| 526 | group2 |

| 527 | group2 |

| 528 | group3 |

| 529 | group2 |

| 530 | group1 |

+-----+--------+

10 rows in set (0.00 sec)

mysql> select c,count(*) from t group by c;

+--------+----------+

| c | count(*) |

+--------+----------+

| group1 | 13 |

| group2 | 19 |

| group3 | 28 |

+--------+----------+

3 rows in set (0.00 sec)

mysql> select c,id from t ,(select @x:=0,@y:=0,@z:=0) x order by

-> case when c='group1' then @x:=@x+1

-> when c='group2' then @y:=@y+1

-> when c='group3' then @z:=@z+1 end

-> ,c limit 20;

+--------+-----+

| c | id |

+--------+-----+

| group1 | 530 |

| group2 | 521 |

| group3 | 523 |

| group1 | 532 |

| group2 | 522 |

| group3 | 525 |

| group1 | 535 |

| group2 | 524 |

| group3 | 528 |

| group1 | 540 |

| group2 | 526 |

| group3 | 533 |

| group1 | 541 |

| group2 | 527 |

| group3 | 538 |

| group1 | 545 |

| group2 | 529 |

| group3 | 539 |

| group1 | 547 |

| group2 | 531 |

+--------+-----+

20 rows in set (0.00 sec)

思路大体是，列出表T,如果group2第一次出现，则这一行标记为grp=1，第二次出现，标记为grp=2，第n次出现则标记为grp=n……group1和group3也同样处理，然后按照grp 和c 排序即可。