MySQL按1个字段随机选择查询

Question

我有表 my_table

id  type  info
1    1    car
2    1    bicycle
3    1    train
4    2    ketchup
5    2    mustard
6    2    soda
7    3    flower
8    3    tree
9    3    grass

我需要进行选择查询，按类型对数据进行排序，这样才能得到类似的结果

type info
1    car
2    ketchup
3    flower

1    bicycle
2    mustard
3    tree

1    train
2    soda
3    grass

现在

SELECT * from my_table ORDER BY type ASC

按类型 111 222 333 订购，我需要类型 123 123 123。

Answer 1

MySQL 没有窗口函数，所以这比在其他数据库中更笨拙。

SELECT type, info
FROM (
    SELECT @rank := IF(type=@type, @rank+1, 1) AS rank, @type:=type AS type, info
    FROM (SELECT @rank:=0, @type:=null) _init, my_table
    ORDER BY type, id
) t
ORDER BY rank, type;

SQL Fiddle