mysql分组随机取数据_MySql分组后随机获取每组一条数据的操作

思路：先随机排序然后再分组就好了。

1、创建表：

create table `xdx_test` (

`id` int(11) not null,

`name` varchar(255) default null,

`class` varchar(255) default null,

primary key (`id`)

) engine=innodb default charset=utf8mb4;

2、插入数据

insert into xdx_test values (1, '张三-1','1');

insert into xdx_test values (2, '李四-1','1');

insert into xdx_test values (3, '王五-1','1');

insert into xdx_test values (4, '张三-2','2');

insert into xdx_test values (5, '李四-2','2');

insert into xdx_test values (6, '王五-2','2');

insert into xdx_test values (7, '张三-3','3');

insert into xdx_test values (8, '李四-3','3');

insert into xdx_test values (9, '王五-3','3');

3、查询语句

select * from

(select * from xdx_test order by rand()) a

group by a.class

4、查询结果

3 王五-1 1

5 李四-2 2

9 王五-3 33 王五-1 1

4 张三-2 2

7 张三-3 32 李四-1 1

5 李四-2 2

8 李四-3 3

补充知识：mysql实现随机获取几条数据的方法(效率和离散型比较)

sql语句有几种写法、效率、以及离散型 比较

1：select * from tablename order by rand() limit 想要获取的数据条数；

2：select *from `table` where id >= (select floor( max(id) * rand()) from `table` ) order by id limit 想要获取的数据条数;

3：select * from `table` as t1 join (select round(rand() * (select max(id) from `table`)) as id) as t2 where t1.id >= t2.id

order by t1.id asc limit 想要获取的数据条数;

4：select