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;
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');
SELECT * FROM (SELECT * FROM xdx_test ORDER BY RAND()) aGROUP BY a.class
3 王五-1 1
5 李四-2 2
9 王五-3 3
3 王五-1 1
4 张三-2 2
7 张三-3 3
2 李四-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 * FROM `table`WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id LIMIT 想要获取的数据条数;
5:SELECT * FROM `table` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) ORDER BY id LIMIT 想要获取的数据条数;
6:SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 想要获取的数据条数;
1的查询时间>>2的查询时间>>5的查询时间>6的查询时间>4的查询时间>3的查询时间,也就是3的效率最高。
更多相关文章
- Android下Excel的操作
- 【Android】文件读写操作(含SDCard的读写)
- android 百度地图3.0+常用操作
- 转:Android下文件操作模式(含SDCard的读写)
- android音频、视频、拍照基础操作
- Android之再谈文件操作和SDcard读写
- android的文件操作。(未整理完成)
- Android中Activity之间访问互传参数
- 转:Android之通信录中的联系人操作