INSERT INTO test (tid,stat,createtime) VALUES ('d987176d350d4fefcc92b9a7ebb4f288',3,'2014-08-20 01:35:52');
COMMIT;
SELECT t3.tid, t3.stat, t3.createtime
FROM (SELECT @gid := @cgid, @cgid := t1.stat, if(@gid = @cgid, @rank := @rank + 1, @rank := 1) AS rank, t1.*
FROM (SELECT *
FROM test
ORDER BY stat, createtime DESC
) t1, (SELECT @gid := 1, @cgid := 1, @rank := 1) t2
) t3
WHERE t3.rank <= 3;
结果:
MySQL实现每个分组随机抽取N个记录的功能
(前面链接中的功能)
SELECT t3.tid, t3.stat, t3.createtime
FROM (SELECT @gid := @cgid, @cgid := t1.stat, if(@gid = @cgid, @rank := @rank + 1, @rank := 1) AS rank, t1.*
FROM (SELECT *
FROM test
ORDER BY stat, rand()
) t1, (SELECT @gid := 1, @cgid := 1, @rank := 1) t2
) t3
WHERE t3.rank <= 3;
----------------------------分割线---------------------------------
上面这些都是使用MySQL自定义变量模拟分析函数,
使用传统的相关子查询也可以实现这个功能,只是效率低
SELECT t1.tid, t1.stat, t1.createtime
FROM test t1
WHERE (
SELECT COUNT(*)
FROM test t2
WHERE t2.stat = t1.stat
AND t1.createtime < t2.createtime
) < 3
ORDER BY stat, createtime DESC
--------------------------------------分割线 --------------------------------------
--------------------------------------分割线 --------------------------------------