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 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的效率最高。

以上6种只是单纯的从效率上做了比较;

上面的6种随机数抽取可分为2类:

第一个的离散型比较高,但是效率低;其他5个都效率比较高,但是存在离散性不高的问题;

怎么解决效率和离散型都满足条件啦?

我们有一个思路就是: 写一个存储过程;

select * FROM test t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM test)-(SELECT MIN(id) FROM test)) + (SELECT MIN(id) FROM test)) AS id) t2 where t1.id >= t2.id limit 1

每次取出一条,然后循环写入一张临时表中;最后返回 select 临时表就OK;

这样既满足了效率又解决了离散型的问题;可以兼并二者的优点;

下面是具体存储过程的伪代码

DROP PROCEDURE IF EXISTS `evaluate_Check_procedure`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `evaluate_Check_procedure`(IN startTime datetime, IN endTime datetime,IN checkNum INT,IN evaInterface VARCHAR(36))
BEGIN

-- 新建一张临时表 ,存放随机取出的数据

create temporary table if not exists xdr_authen_tmp (
 `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
 `LENGTH` int(5) DEFAULT NULL COMMENT '字节数',
 `INTERFACE` int(3) NOT NULL COMMENT '接口',
 `XDR_ID` varchar(32) NOT NULL COMMENT 'XDR ID',
 `MSISDN` varchar(32) DEFAULT NULL COMMENT '用户号码',
 `PROCEDURE_START_TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '开始时间',
 `PROCEDURE_END_TIME` datetime DEFAULT NULL COMMENT '结束时间',
 `SOURCE_NE_IP` varchar(39) DEFAULT NULL COMMENT '源网元IP',
 `SOURCE_NE_PORT` int(5) DEFAULT NULL COMMENT '源网元端口',
 `DESTINATION_NE_IP` varchar(39) DEFAULT NULL COMMENT '目的网元IP',
 `DESTINATION_NE_PORT` int(5) DEFAULT NULL COMMENT '目的网元端口',
 `INSERT_DATE` datetime DEFAULT NULL COMMENT '插入时间',
 `EXTEND1` varchar(50) DEFAULT NULL COMMENT '扩展1',
 `EXTEND2` varchar(50) DEFAULT NULL COMMENT '扩展2',
 `EXTEND3` varchar(50) DEFAULT NULL COMMENT '扩展3',
 `EXTEND4` varchar(50) DEFAULT NULL COMMENT '扩展4',
 `EXTEND5` varchar(50) DEFAULT NULL COMMENT '扩展5',
 PRIMARY KEY (`ID`,`PROCEDURE_START_TIME`),
 KEY `index_procedure_start_time` (`PROCEDURE_START_TIME`),
 KEY `index_source_dest_ip` (`SOURCE_NE_IP`,`DESTINATION_NE_IP`),
 KEY `index_xdr_id` (`XDR_ID`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

BEGIN
DECLARE j INT;
DECLARE i INT;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET i = 1;

-- 这里的checkNum是需要随机获取的数据数,比如随机获取10条,那这里就是10,通过while循环来逐个获取单个随机记录;

SET j = 0;
WHILE j < checkNum DO
 set @sqlexi = concat( ' SELECT t1.ID,t1.LENGTH,t1.LOCAL_PROVINCE,t1.LOCAL_CITY,t1.OWNER_PROVINCE,t1.OWNER_CITY,t1.ROAMING_TYPE,t1.INTERFACE,t1.XDR_ID,t1.RAT,t1.IMSI,t1.IMEI,t1.MSISDN,t1.PROCEDURE_START_TIME,t1.PROCEDURE_END_TIME,t1.TRANSACTION_TYPE,t1.TRANSACTION_STATUS,t1.SOURCE_NE_IP,t1.SOURCE_NE_PORT,t1.DESTINATION_NE_IP,t1.DESTINATION_NE_PORT,t1.RESULT_CODE,t1.EXPERIMENTAL_RESULT_CODE,t1.ORIGIN_REALM,t1.DESTINATION_REALM,t1.ORIGIN_HOST,t1.DESTINATION_HOST,t1.INSERT_DATE',
    ' into @ID,@LENGTH,@LOCAL_PROVINCE,@LOCAL_CITY,@OWNER_PROVINCE,@OWNER_CITY,@ROAMING_TYPE,@INTERFACE,@XDR_ID,@RAT,@IMSI,@IMEI,@MSISDN,@PROCEDURE_START_TIME,@PROCEDURE_END_TIME,@TRANSACTION_TYPE,@TRANSACTION_STATUS,@SOURCE_NE_IP,@SOURCE_NE_PORT,@DESTINATION_NE_IP,@DESTINATION_NE_PORT,@RESULT_CODE,@EXPERIMENTAL_RESULT_CODE,@ORIGIN_REALM,@DESTINATION_REALM,@ORIGIN_HOST,@DESTINATION_HOST,@INSERT_DATE ',
    ' FROM xdr_authen t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM xdr_authen)-(SELECT MIN(id) FROM xdr_authen)) + (SELECT MIN(id) FROM xdr_authen)) AS id) t2',
    ' WHERE t1.PROCEDURE_START_TIME >= "',startTime,'"',
       ' AND t1.PROCEDURE_START_TIME < "',endTime,'"',' AND t1.INTERFACE IN (',evaInterface,')',
       ' and t1.id >= t2.id limit 1');
 PREPARE sqlexi FROM @sqlexi;
 EXECUTE sqlexi;
 DEALLOCATE PREPARE sqlexi;

-- 这里获取的记录有可能会重复,如果是重复数据,我们则不往临时表中插入此条数据,再进行下一次随机数据的获取。依次类推,直到随机数据取够为止;

 select count(1) into @num from xdr_authen_tmp where id = @ID;

 if @num > 0 or i=1 then
  SET j = j;
 ELSE
  insert into xdr_authen_tmp(ID,LENGTH,LOCAL_PROVINCE,LOCAL_CITY,OWNER_PROVINCE,OWNER_CITY,ROAMING_TYPE,INTERFACE,XDR_ID,RAT,IMSI,IMEI,MSISDN,PROCEDURE_START_TIME,PROCEDURE_END_TIME,TRANSACTION_TYPE,TRANSACTION_STATUS,SOURCE_NE_IP,SOURCE_NE_PORT,DESTINATION_NE_IP,DESTINATION_NE_PORT,RESULT_CODE,EXPERIMENTAL_RESULT_CODE,ORIGIN_REALM,DESTINATION_REALM,ORIGIN_HOST,DESTINATION_HOST,INSERT_DATE)
  VALUES(@ID,@LENGTH,@LOCAL_PROVINCE,@LOCAL_CITY,@OWNER_PROVINCE,@OWNER_CITY,@ROAMING_TYPE,@INTERFACE,@XDR_ID,@RAT,@IMSI,@IMEI,@MSISDN,@PROCEDURE_START_TIME,@PROCEDURE_END_TIME,@TRANSACTION_TYPE,@TRANSACTION_STATUS,@SOURCE_NE_IP,@SOURCE_NE_PORT,@DESTINATION_NE_IP,@DESTINATION_NE_PORT,@RESULT_CODE,@EXPERIMENTAL_RESULT_CODE,@ORIGIN_REALM,@DESTINATION_REALM,@ORIGIN_HOST,@DESTINATION_HOST,@INSERT_DATE);

  SET j = j + 1;
 end if;
 SET i=0;

END WHILE;

-- 最后我们将所有的随机数查询出来,以结果集的形式返回给后台

select ID,LENGTH,LOCAL_PROVINCE,LOCAL_CITY,OWNER_PROVINCE,OWNER_CITY,ROAMING_TYPE,INTERFACE,XDR_ID,RAT,IMSI,IMEI,MSISDN,PROCEDURE_START_TIME,PROCEDURE_END_TIME,TRANSACTION_TYPE,TRANSACTION_STATUS,SOURCE_NE_IP,SOURCE_NE_PORT,DESTINATION_NE_IP,DESTINATION_NE_PORT,RESULT_CODE,EXPERIMENTAL_RESULT_CODE,ORIGIN_REALM,DESTINATION_REALM,ORIGIN_HOST,DESTINATION_HOST,INSERT_DATE from xdr_authen_tmp;

END;
truncate TABLE xdr_authen_tmp;

END
;;
DELIMITER ;

以上这篇MySql分组后随机获取每组一条数据的操作就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们。

(0)

相关推荐

  • 基于mysql实现group by取各分组最新一条数据

    前言: group by函数后取到的是分组中的第一条数据,但是我们有时候需要取出各分组的最新一条,该怎么实现呢? 本文提供两种实现方式. 一.准备数据 http://note.youdao.com/noteshare?id=dba748092a619be0a8f160ccf6e25a5f&sub=FD4C1C7823CA440DB360FEA3B4A905CD 二.三种实现方式 1)先order by之后再分组: SELECT * FROM (SELECT * from tb_dept ORDE

  • Mysql查询最近一条记录的sql语句(优化篇)

    下策--查询出结果后将时间排序后取第一条 select * from a where create_time<="2017-03-29 19:30:36" order by create_time desc limit 1 这样做虽然可以取出当前时间最近的一条记录,但是一次查询需要将表遍历一遍,对于百万以上数据查询将比较费时:limit是先取出全部结果,然后取第一条,相当于查询中占用了不必要的时间和空间:还有如果需要批量取出最近一条记录,比方说:"一个订单表,有用户,订

  • mysql随机查询若干条数据的方法

    在mysql中查询5条不重复的数据,使用以下: 复制代码 代码如下: SELECT * FROM `table` ORDER BY RAND() LIMIT 5 就可以了.但是真正测试一下才发现这样效率非常低.一个15万余条的库,查询5条数据,居然要8秒以上搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据. 复制代码 代码如下: SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX

  • 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','

  • Mysql字符串截取及获取指定字符串中的数据

    前言:本人遇到一个需求,需要在MySql的字段中截取一段字符串中的特定字符,类似于正则表达式的截取,苦于没有合适的方法,百度之后终于找到一个合适的方法:substring_index('www.sqlstudy.com.cn', '.', -2) 强烈推荐该方法获取含有特定字符的数据. substring_index(input,split,index):input为要截取的字符,split为分隔符,Index为要截取第index个分隔符左(index为正)或右(index为负)的字符串. 拿个

  • MySQL如何利用存储过程快速生成100万条数据详解

    1.在测试的时候为了测试大数据量的情况下项目的抗压能力我们通常要创造一些测试数据那么现在这个方法绝对好用 其中可能会有sql空间的报错可以自己尝试解决,这里做了分批插入,每次插入30万条,所以没有遇到类似的空间问题 首先,创建要插入100万数据的表格 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for sdb_b2c_orders -- ----

  • mysql分组后合并显示一个字段的多条数据方式

    目录 mysql分组合并显示一个字段多条数据 表中数据如下 mysql多字段分组 mysql多字段分组 GROUP BY与ORDER BY一起使用(分组排序) 使用having过滤分组 mysql分组合并显示一个字段多条数据 首先我们有一张学生兴趣表(student_hobby),字段包括主键(id).姓名(name).兴趣(hobby) 表中数据如下 id name hobby 0 小明 篮球 1 小明 跑步 2 小华 读书 然后我们想让他显示成如下效果. name hobbies 小明 篮球

  • php 利用array_slice函数获取随机数组或前几条数据

    先给大家说下基本语法: array_slice ( array $array , int $offset [, int $length [, bool $preserve_keys ]] ) array_slice() 返回根据 offset 和 length 参数所指定的 array 数组中的一段序列. 如果 offset 非负,则序列将从 array 中的此偏移量开始.如果 offset 为负,则序列将从 array 中距离末端这么远的地方开始. 如果给出了 length 并且为正,则序列中

  • mysql 一次向表中插入多条数据实例讲解

    mysql一次插入多条数据: INSERT INTO hk_test(username, passwd) VALUES ('qmf2', 'qmf2'),('qmf3', 'qmf3'),('qmf4', 'qmf4'),('qmf5', 'qmf5') GO 我们先来创建一种表Authors: CREATE TABLE Authors( AuthID SMALLINT NOT NULL PRIMARY KEY, AuthFN VARCHAR(20), AuthMN VARCHAR(20), A

  • MySql中使用INSERT INTO语句更新多条数据的例子

    我们知道当插入多条数据的时候insert支持多条语句: 复制代码 代码如下: INSERT INTO t_member (id, name, email) VALUES     (1, 'nick', 'nick@126.com'),     (4, 'angel','angel@163.com'),     (7, 'brank','ba198@126.com'); 但是对于更新记录,由于update语法不支持一次更新多条记录,只能一条一条执行: 复制代码 代码如下: UPDATE t_mem

  • SQL语句分组获取记录的第一条数据的方法

    使用Northwind 数据库 首先查询Employees表 查询结果: city列里面只有5个城市 使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 先进行分组 注:根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的). sql语句为: select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,City,ROW_NUM

  • mysql获取分组后每组的最大值实例详解

     mysql获取分组后每组的最大值实例详解 1. 测试数据库表如下: create table test ( `id` int not null auto_increment, `name` varchar(20) not null default '', `score` int not null default 0, primary key(`id`) )engine=InnoDB CHARSET=UTF8; 2. 插入如下数据: mysql> select * from test; +---

  • 分组后分组合计以及总计SQL语句(稍微整理了一下)

    今天看到了这个文章感觉内容挺多的,就是比较乱,实在不好整理,我们小编就简单整理了一下,希望大家能凑合看吧 分组后分组合计以及总计SQL语句   1)想一次性得到分组合计以及总计,sql: SELECT 分组字段 FROM 表 GROUP BY 分组字段 compute sum(COUNT(*)) 2)分组合计1: SELECT COUNT(*) FROM (SELECT 分组字段 FROM 表 GROUP BY 分组字段 )别名 3)分组合计2: SELECT COUNT(*) FROM (SE

随机推荐