mysql 分组后随机取3条，不够的全部取出来，应该怎么写？

我这里给你一个 类似的例子, 表结构也就是简单的 分类, 明细.要查询每个分类下面, 随机明细 几条的情况.你可以参考参考.测试表与测试数据。CREATE TABLE test_order_by_rand (group_code varchar(10), name varchar(10))INSERT INTO test_order_by_rand VALUES('水果', '苹果')INSERT INTO test_order_by_rand VALUES('水果', '梨子')INSERT INTO test_order_by_rand VALUES('水果', '香蕉')INSERT INTO test_order_by_rand VALUES('水果', '桃子')INSERT INTO test_order_by_rand VALUES('水果', '菠萝')INSERT INTO test_order_by_rand VALUES('蔬菜', '萝卜')INSERT INTO test_order_by_rand VALUES('蔬菜', '白菜')INSERT INTO test_order_by_rand VALUES('蔬菜', '蘑菇')INSERT INTO test_order_by_rand VALUES('蔬菜', '毛豆')INSERT INTO test_order_by_rand VALUES('蔬菜', '青椒')INSERT INTO test_order_by_rand VALUES('坚果', '核桃')INSERT INTO test_order_by_rand VALUES('坚果', '榛子')INSERT INTO test_order_by_rand VALUES('坚果', '杏仁')INSERT INTO test_order_by_rand VALUES('坚果', '花生')INSERT INTO test_order_by_rand VALUES('坚果', '开心果')预期目标， 按照 group_code 进行分组， 每个分组内， 进行随机获取数据.SELECT group_code, nameFROM(SELECT CASE WHEN @cn != group_code THEN @rownum:= 1 ELSE @rownum:= @rownum + 1 END AS No, group_code, @cn := group_code AS group_code, nameFROM (SELECT @rownum:=0) r, (SELECT @cn:= '') p, (SELECT * FROM test_order_by_rand order by group_code, rand()) tmp) subQueryWHERE No <= 3ORDER BY group_code这里套了2层子查询， 其中的(SELECT * FROM test_order_by_rand order by group_code, rand()) tmp 用于实现， 针对每一个 group_code 进行分组， 下属的 name 的数据， 进行随机排序其中的(......) subQuery用于实现， 针对 前面的 按 group_code 分组， name 随机排序后的数据。模拟一个 row_number() over(partition by group_code) 的处理最后的 No <= 3 用于限制， 每一个 group_code 下面， 显示多少行.执行结果：+------------+--------+| group_code | name |+------------+--------+| 水果 | 苹果 || 水果 | 香蕉 || 水果 | 梨子 || 蔬菜 | 青椒 || 蔬菜 | 白菜 || 蔬菜 | 蘑菇 || 坚果 | 核桃 || 坚果 | 花生 || 坚果 | 开心果 |+------------+--------+9 rows in set (0.00 sec)SELECTgroup_code,`name`FROM(SELECTCASEWHEN @cn != group_code THEN @rownum:= 1ELSE @rownum:= @rownum + 1END AS No,group_code,@cn := group_code AS group_code1,nameFROM(SELECT @rownum:=0) r,(SELECT @cn:= '') p,

CREATE TABLE test_gd2gd2(

idINT,

type INT,

str CHAR(3)

)

INSERT INTO test_gd2gd2

SELECT 1, 0, 'aaa' UNION ALL

SELECT 2, 0, 'bbb' UNION ALL

SELECT 3, 1, 'sss' UNION ALL

SELECT 4, 1, 'ddd' UNION ALL

SELECT 5, 2, 'ggg' UNION ALL

SELECT 6, 2, 'hhh'

mysql>SELECT

-> type,

-> (SELECT str FROM test_gd2gd2 sub

->WHERE type = main.type ORDER BY rand()

-> LIMIT 0,1) AS Rstr

->FROM

-> test_gd2gd2 main

->GROUP BY

-> type

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

| type | Rstr |

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

|0 | bbb |

|1 | ddd |

|2 | hhh |

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

3 rows in set (0.01 sec)

mysql>SELECT

-> type,

-> (SELECT str FROM test_gd2gd2 sub

->WHERE type = main.type ORDER BY rand()

-> LIMIT 0,1) AS Rstr

->FROM

-> test_gd2gd2 main

->GROUP BY

-> type

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

| type | Rstr |

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

|0 | aaa |

|1 | sss |

|2 | hhh |

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

3 rows in set (0.00 sec)

---