MySql分組后隨機獲取每組一條數(shù)據(jù)的操作

更新時間：2020年10月23日 10:22:21 作者：小道仙

這篇文章主要介紹了MySql分組后隨機獲取每組一條數(shù)據(jù)的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

思路：先隨機排序然后再分組就好了。

1、創(chuàng)建表：

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、插入數(shù)據(jù)

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實現(xiàn)隨機獲取幾條數(shù)據(jù)的方法（效率和離散型比較）

sql語句有幾種寫法、效率、以及離散型比較

1：SELECT * FROM tablename ORDER BY RAND() LIMIT 想要獲取的數(shù)據(jù)條數(shù)；

2：SELECT *FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 想要獲取的數(shù)據(jù)條數(shù);

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 想要獲取的數(shù)據(jù)條數(shù);

4：SELECT * FROM `table`WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id LIMIT 想要獲取的數(shù)據(jù)條數(shù);

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 想要獲取的數(shù)據(jù)條數(shù);

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 想要獲取的數(shù)據(jù)條數(shù);

1的查詢時間>>2的查詢時間>>5的查詢時間>6的查詢時間>4的查詢時間>3的查詢時間，也就是3的效率最高。

以上6種只是單純的從效率上做了比較；

上面的6種隨機數(shù)抽取可分為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

每次取出一條，然后循環(huán)寫入一張臨時表中；最后返回 select 臨時表就OK；

這樣既滿足了效率又解決了離散型的問題；可以兼并二者的優(yōu)點；

下面是具體存儲過程的偽代碼

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

-- 新建一張臨時表 ,存放隨機取出的數(shù)據(jù)

create temporary table if not exists xdr_authen_tmp ( 
 `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序號',
 `LENGTH` int(5) DEFAULT NULL COMMENT '字節(jié)數(shù)',
 `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 '源網(wǎng)元IP',
 `SOURCE_NE_PORT` int(5) DEFAULT NULL COMMENT '源網(wǎng)元端口',
 `DESTINATION_NE_IP` varchar(39) DEFAULT NULL COMMENT '目的網(wǎng)元IP',
 `DESTINATION_NE_PORT` int(5) DEFAULT NULL COMMENT '目的網(wǎng)元端口',
 `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是需要隨機獲取的數(shù)據(jù)數(shù)，比如隨機獲取10條，那這里就是10，通過while循環(huán)來逐個獲取單個隨機記錄；

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;

-- 這里獲取的記錄有可能會重復，如果是重復數(shù)據(jù)，我們則不往臨時表中插入此條數(shù)據(jù)，再進行下一次隨機數(shù)據(jù)的獲取。依次類推，直到隨機數(shù)據(jù)取夠為止；

 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;

-- 最后我們將所有的隨機數(shù)查詢出來，以結果集的形式返回給后臺

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分組后隨機獲取每組一條數(shù)據(jù)的操作就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

MySQL索引命中與失效代碼實現(xiàn)
這篇文章主要介紹了MySQL索引命中與失效代碼實現(xiàn)，文章內(nèi)容詳細，簡單易懂,需要的朋友可以參考下
2023-01-01
一步步教你利用Mysql存儲過程造百萬級數(shù)據(jù)
因工作需要維護一張中建表數(shù)據(jù)內(nèi)置,所以得造數(shù)據(jù)所以使用存儲過程來造數(shù)據(jù),下面這篇文章主要給大家介紹了關于如何一步步利用Mysql存儲過程造百萬級數(shù)據(jù)的相關資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下
2023-03-03
關于MySQL查詢語句的優(yōu)化詳解
這篇文章主要介紹了MySQL查詢語句的優(yōu)化方法，分別介紹了子查詢優(yōu)化，分頁查詢優(yōu)化以及排序查詢優(yōu)化，對學習有一定的幫助，需要的小伙伴可以參考一下
2023-04-04
安裝配置Zabbix來監(jiān)控MySQL的基本教程
這篇文章主要介紹了安裝配置Zabbix來監(jiān)控MySQL的基本教程,Zabbix擁有web頁面端顯示數(shù)據(jù)的功能,文中的安裝環(huán)境為CentOS系統(tǒng),需要的朋友可以參考下
2015-12-12
MySQL查看數(shù)據(jù)庫狀態(tài)命令詳細講解
在工作中，有時候我們需要了解MySQL服務器的狀態(tài)信息,下面這篇文章主要給大家介紹了關于MySQL查看數(shù)據(jù)庫狀態(tài)命令的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
2023-03-03
mysql left join的基本用法以及on與where的區(qū)別
我們在寫sql語句的時候，總是無法避免使用到連接關鍵詞，比如內(nèi)連接、外連接，下面就是詳細的介紹,需要的朋友可以參考下
2023-05-05
mysql中邏輯函數(shù)的具體使用
MySQL中邏輯函數(shù)包括IF、CASE、COALESCE、NULLIF和IFNULL,用于根據(jù)條件對數(shù)據(jù)進行判斷和選擇,適用于復雜的數(shù)據(jù)處理需求,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2024-11-11
MySQL中truncate誤操作后的數(shù)據(jù)恢復案例
這篇文章主要介紹了MySQL中truncate誤操作后的數(shù)據(jù)恢復案例,主要是要從日志中定位到truncate操作的地方然后備份之前丟失的數(shù)據(jù),需要的朋友可以參考下
2015-05-05
MySQL數(shù)據(jù)優(yōu)化-多層索引
這篇文章主要介紹了MySQL數(shù)據(jù)優(yōu)化?多層索引，文章圍繞MySQL數(shù)據(jù)優(yōu)化?多層索引的相關資料展開詳細的內(nèi)容，具有一定的參考價值，需要的小伙伴可以參考一下
2021-12-12
MySQL中slave_exec_mode參數(shù)詳解
本篇文章主要給大家講述了MySQL中slave_exec_mode參數(shù)的用法以及示例分析了出現(xiàn)的錯誤問題和解決辦法，需要的朋友參考學習下吧。
2017-12-12