MySQL：获取随机记录

相关贴子：

https://bbs.csdn.net/topics/392500601?page=1#post-403631172

DROP TABLE IF EXISTS t; 
CREATE TABLE t(
id bigint
,label VARCHAR(20)
,n INT	
);
INSERT INTO t VALUES('1000000000000059993','s1','4');
INSERT INTO t VALUES('1000000000000925982','s1','4');
INSERT INTO t VALUES('1000000000001199348','s1','4');
INSERT INTO t VALUES('1000000000001638782','s1','4');
INSERT INTO t VALUES('1000000000001682891','s1','4');
INSERT INTO t VALUES('1000000000002584699','s1','4');
INSERT INTO t VALUES('1000000000000079389','s1','4');
INSERT INTO t VALUES('1000000000001217799','s1','4');
INSERT INTO t VALUES('101050481232942966','s1','4');
INSERT INTO t VALUES('676549513128752044','s1','4');
INSERT INTO t VALUES('890249072426673466','s1','4');
INSERT INTO t VALUES('212745469495313447','s4','2');
INSERT INTO t VALUES('510346678931418680','s4','2');
INSERT INTO t VALUES('803746678931739658','s4','2');
INSERT INTO t VALUES('670647327506531562','s4','2');
INSERT INTO t VALUES('1000000000003045625','s4','2');
INSERT INTO t VALUES('1000000000002091492','s4','2');
INSERT INTO t VALUES('1000000000002856240','s4','2');
INSERT INTO t VALUES('1000000000003058272','s4','2');
INSERT INTO t VALUES('1000000000000734975','s10','3');
INSERT INTO t VALUES('1000000000002910312','s10','3');
INSERT INTO t VALUES('1000000000000260015','s10','3');
INSERT INTO t VALUES('1000000000001252400','s10','3');
INSERT INTO t VALUES('266251025098187409','s10','3');
INSERT INTO t VALUES('509450463862531415','s10','3');
INSERT INTO t VALUES('845751401207367664','s10','3');
INSERT INTO t VALUES('1000000000002407715','s10','3');
INSERT INTO t VALUES('1000000000002940425','s10','3');

#1. 表上创建索引
create index ix_t_label_id on t (label,id);

#2. 增加序数表, 如果需要更多数值，自己添加
drop table if exists seq;
create table seq(
id int
);
insert into seq values(1);
insert into seq values(2);
insert into seq values(3);
insert into seq values(4);
insert into seq values(5);
insert into seq values(6);
insert into seq values(7);
insert into seq values(8);
insert into seq values(9);
insert into seq values(10);

#3. 创建临时表
drop TEMPORARY table if exists tmp;
create TEMPORARY table tmp(
   label varchar(20),
	 rid   int,
	 primary key(label,rid)
);
#4. 插入生成好的 随机数及对应的位置 的数据到临时表
insert into tmp
select label,FLOOR(1+rand(3)*(cnt-1)) as rid from (
select 
id
,label
,count(1) as cnt
,case when label='s1' then 4 
when label='s4' then 2
when label='s10' then 3
else 0 end
as randomNum
from t
where label in ('s1','s4','s10')
group by label
) as tt cross join seq on tt.randomNum>=seq.id
order by label;
#select * from tmp;

#原表以 label 分组排名得到rid, 根据连接获取需要数据
select a.id
,a.label
,a.n
from (
select * 
,(select count(1) from t as b where a.label=b.label and a.id>=b.id) as rid
from t as a
) as a inner join tmp on a.label=tmp.label and a.rid=tmp.rid
order by a.label,a.rid ;

/*
+---------------------+-------+---+
| id                  | label | n |
+---------------------+-------+---+
|  676549513128752044 | s1    | 4 |
| 1000000000000059993 | s1    | 4 |
| 1000000000001199348 | s1    | 4 |
| 1000000000001682891 | s1    | 4 |
|  509450463862531415 | s10   | 3 |
| 1000000000000260015 | s10   | 3 |
| 1000000000001252400 | s10   | 3 |
| 1000000000002091492 | s4    | 2 |
| 1000000000003045625 | s4    | 2 |
+---------------------+-------+---+
*/