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

2023-07-13 10:02:06 226

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

1、创建表：

CREATETABLE`xdx_test`(
`id`int(11)NOTNULL,
`name`varchar(255)DEFAULTNULL,
`class`varchar(255)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

2、插入数据

INSERTINTOxdx_testVALUES(1,'张三-1','1');
INSERTINTOxdx_testVALUES(2,'李四-1','1');
INSERTINTOxdx_testVALUES(3,'王五-1','1');
INSERTINTOxdx_testVALUES(4,'张三-2','2');
INSERTINTOxdx_testVALUES(5,'李四-2','2');
INSERTINTOxdx_testVALUES(6,'王五-2','2');
INSERTINTOxdx_testVALUES(7,'张三-3','3');
INSERTINTOxdx_testVALUES(8,'李四-3','3');
INSERTINTOxdx_testVALUES(9,'王五-3','3');

3、查询语句

SELECT*FROM
(SELECT*FROMxdx_testORDERBYRAND())a
GROUPBYa.class

4、查询结果

3王五-11

5李四-22

9王五-33

3王五-11

4张三-22

7张三-33

2李四-11

5李四-22

8李四-33

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

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

1：SELECT*FROMtablenameORDERBYRAND()LIMIT想要获取的数据条数；

2：SELECT*FROM`table`WHEREid>=(SELECTFLOOR(MAX(id)*RAND())FROM`table`)ORDERBYidLIMIT想要获取的数据条数;

3：SELECT*FROM`table`ASt1JOIN(SELECTROUND(RAND()*(SELECTMAX(id)FROM`table`))ASid)ASt2WHEREt1.id>=t2.id

ORDERBYt1.idASCLIMIT想要获取的数据条数;

4：SELECT*FROM`table`WHEREid>=(SELECTfloor(RAND()*(SELECTMAX(id)FROM`table`)))ORDERBYidLIMIT想要获取的数据条数;

5：SELECT*FROM`table`WHEREid>=(SELECTfloor(RAND()*((SELECTMAX(id)FROM`table`)-(SELECTMIN(id)FROM`table`))+(SELECTMIN(id)FROM`table`)))ORDERBYidLIMIT想要获取的数据条数;

6：SELECT*FROM`table`ASt1JOIN(SELECTROUND(RAND()*((SELECTMAX(id)FROM`table`)-(SELECTMIN(id)FROM`table`))+(SELECTMIN(id)FROM`table`))ASid)ASt2WHEREt1.id>=t2.idORDERBYt1.idLIMIT想要获取的数据条数;

1的查询时间>>2的查询时间>>5的查询时间>6的查询时间>4的查询时间>3的查询时间，也就是3的效率最高。

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

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

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

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

我们有一个思路就是：写一个存储过程；

select*FROMtestt1JOIN(SELECTROUND(RAND()*((SELECTMAX(id)FROMtest)-(SELECTMIN(id)FROMtest))+(SELECTMIN(id)FROMtest))ASid)t2wheret1.id>=t2.idlimit1

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

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

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

DROPPROCEDUREIFEXISTS`evaluate_Check_procedure`;
DELIMITER;;
CREATEDEFINER=`root`@`%`PROCEDURE`evaluate_Check_procedure`(INstartTimedatetime,INendTimedatetime,INcheckNumINT,INevaInterfaceVARCHAR(36))
BEGIN

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

createtemporarytableifnotexistsxdr_authen_tmp(
`ID`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'序号',
`LENGTH`int(5)DEFAULTNULLCOMMENT'字节数',
`INTERFACE`int(3)NOTNULLCOMMENT'接口',
`XDR_ID`varchar(32)NOTNULLCOMMENT'XDRID',
`MSISDN`varchar(32)DEFAULTNULLCOMMENT'用户号码',
`PROCEDURE_START_TIME`datetimeNOTNULLDEFAULT'0000-00-0000:00:00'COMMENT'开始时间',
`PROCEDURE_END_TIME`datetimeDEFAULTNULLCOMMENT'结束时间',
`SOURCE_NE_IP`varchar(39)DEFAULTNULLCOMMENT'源网元IP',
`SOURCE_NE_PORT`int(5)DEFAULTNULLCOMMENT'源网元端口',
`DESTINATION_NE_IP`varchar(39)DEFAULTNULLCOMMENT'目的网元IP',
`DESTINATION_NE_PORT`int(5)DEFAULTNULLCOMMENT'目的网元端口',
`INSERT_DATE`datetimeDEFAULTNULLCOMMENT'插入时间',
`EXTEND1`varchar(50)DEFAULTNULLCOMMENT'扩展1',
`EXTEND2`varchar(50)DEFAULTNULLCOMMENT'扩展2',
`EXTEND3`varchar(50)DEFAULTNULLCOMMENT'扩展3',
`EXTEND4`varchar(50)DEFAULTNULLCOMMENT'扩展4',
`EXTEND5`varchar(50)DEFAULTNULLCOMMENT'扩展5',
PRIMARYKEY(`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=InnoDBDEFAULTCHARSET=utf8;

BEGIN
DECLAREjINT;
DECLAREiINT;

DECLARECONTINUEHANDLERFORNOTFOUNDSETi=1;

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

SETj=0;
WHILEj="',startTime,'"',
'ANDt1.PROCEDURE_START_TIME<"',endTime,'"','ANDt1.INTERFACEIN(',evaInterface,')',
'andt1.id>=t2.idlimit1');
PREPAREsqlexiFROM@sqlexi;
EXECUTEsqlexi;
DEALLOCATEPREPAREsqlexi;

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

selectcount(1)into@numfromxdr_authen_tmpwhereid=@ID;

if@num>0ori=1then
SETj=j;
ELSE
insertintoxdr_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);

SETj=j+1;
endif;
SETi=0;

ENDWHILE;

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

selectID,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_DATEfromxdr_authen_tmp;

END;
truncateTABLExdr_authen_tmp;

END
;;
DELIMITER;

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

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

热门推荐

随机推荐