MySQL使用自定义变量模拟分析函数

MySQL使用自定义变量模拟分析函数，初始化实验结构和数据：create table test（

tid varchar（32） primary key, stat int not null, createtime timestamp not null）;INSERT INTO test （tid,stat,createtime） VALUES （"ac551ad7ba3f9067b19ac8bb20caca6d",-1,"2014-08-20 10:01:09"）;INSERT INTO test （tid,stat,createtime） VALUES （"dc9a4438e577f4b08f7033a305544d47",-1,"2014-08-20 10:00:19"）;INSERT INTO test （tid,stat,createtime） VALUES （"23055228532bbba5a68d6ada11bcf33f",-1,"2014-08-20 09:58:32"）;INSERT INTO test （tid,stat,createtime） VALUES （"5711ee1610d07a55e64c7948667de6e8",-1,"2014-08-20 09:58:09"）;INSERT INTO test （tid,stat,createtime） VALUES （"035e06d8afd681a9904bd74e9860f8cb",-1,"2014-08-20 09:57:52"）;INSERT INTO test （tid,stat,createtime） VALUES （"3890efc08f37fa489a4e130cb04f71ac",-1,"2014-08-20 09:57:48"）;INSERT INTO test （tid,stat,createtime） VALUES （"1b6ed9db663dae470b45c722a61d08b0",-1,"2014-08-20 09:56:40"）;INSERT INTO test （tid,stat,createtime） VALUES （"8fb3409015e6b2cf85ba6ee90f15b58f",-1,"2014-08-20 09:54:40"）;INSERT INTO test （tid,stat,createtime） VALUES （"0badb1f4c2b1a89f1c473b992183add3",-1,"2014-08-20 09:54:33"）;INSERT INTO test （tid,stat,createtime） VALUES （"89b8af5eb473b2d4f50dd9e10773a9cc",-1,"2014-08-20 09:53:54"）;INSERT INTO test （tid,stat,createtime） VALUES （"77923a7397110224b5f94e7d0bd297de",2,"2014-08-19 17:13:17"）;INSERT INTO test （tid,stat,createtime） VALUES （"0df1da77cfdbe64edcd4d645197174af",2,"2014-08-19 12:20:21"）;INSERT INTO test （tid,stat,createtime） VALUES （"43daef6bfbc46dbfdbb97e74173dab30",2,"2014-08-19 09:54:08"）;INSERT INTO test （tid,stat,createtime） VALUES （"d5d12c510391314f48054c6c9ab9535c",2,"2014-08-19 09:23:41"）;INSERT INTO test （tid,stat,createtime） VALUES （"f7c123143752498b7c9a226a9583ae49",2,"2014-08-19 01:14:21"）;INSERT INTO test （tid,stat,createtime） VALUES （"da6a9a78897a42ae0a565cd0fabd76bb",2,"2014-08-18 21:59:46"）;INSERT INTO test （tid,stat,createtime） VALUES （"9cd3f83ab04120504a880523702491d7",2,"2014-08-18 16:26:30"）;INSERT INTO test （tid,stat,createtime） VALUES （"4dfa129ba64e7062afa37e56bb9632de",2,"2014-08-18 14:32:41"）;INSERT INTO test （tid,stat,createtime） VALUES （"a9a731870e1c02278c22ce1ab36fa43c",2,"2014-08-18 14:31:26"）;INSERT INTO test （tid,stat,createtime） VALUES （"97f39d2a1e519f99e602e72cfc45fe0c",2,"2014-08-17 11:47:52"）;INSERT INTO test （tid,stat,createtime） VALUES （"31ba95265a96971221ddf9320c79eed8",3,"2014-08-20 02:08:50"）;INSERT INTO test （tid,stat,createtime） VALUES （"060d92222edcb6f583cb4cd0244aadc0",3,"2014-08-20 02:05:54"）;INSERT INTO test （tid,stat,createtime） VALUES （"7d3eb4ea201906b08e961b9fe7726fd4",3,"2014-08-20 02:00:11"）;INSERT INTO test （tid,stat,createtime） VALUES （"c633bc16cb8c3bb4ffa7f00682701b92",3,"2014-08-20 01:54:22"）;INSERT INTO test （tid,stat,createtime） VALUES （"e43bb7e7274259712b389e3feabc068f",3,"2014-08-20 01:49:36"）;INSERT INTO test （tid,stat,createtime） VALUES （"bdabf3d80fb097222112cb30cdc48117",3,"2014-08-20 01:48:48"）;INSERT INTO test （tid,stat,createtime） VALUES （"170e2bdc11d517a56b7ce23d85633e42",3,"2014-08-20 01:46:56"）;INSERT INTO test （tid,stat,createtime） VALUES （"7e79f6065ae8bb215cee43a4efbcd852",3,"2014-08-20 01:44:17"）;INSERT INTO test （tid,stat,createtime） VALUES （"04728676e3305de05a18333ddfc76c01",3,"2014-08-20 01:39:05"）;INSERT INTO test （tid,stat,createtime） VALUES （"d987176d350d4fefcc92b9a7ebb4f288",3,"2014-08-20 01:35:52"）;COMMIT;要求:
Stat表示状态,Createtime表示创建时间
查询每种状态最近3个记录的内容

这个需求似曾相识 http://www.linuxidc.com/Linux/2014-08/105900.htm

但是这回是MySQL数据库了。他没有分析函数，只能用自定义变量模拟
test表的stat字段为分组标识
gid是上一个记录的分组标识,
cgid是当前记录的分组标识,
如果gid和cgid不等，说明分组标识已经变了,Rank排序重置
这样Rank是按照每个分组进行自增的,取前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, createtime DESC ） t1, （SELECT @gid := 1, @cgid := 1, @rank := 1） t2
） t3WHERE 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
） t3WHERE t3.rank <= 3;

----------------------------分割线---------------------------------

上面这些都是使用MySQL自定义变量模拟分析函数,
使用传统的相关子查询也可以实现这个功能,只是效率低

SELECT t1.tid, t1.stat, t1.createtime

FROM test t1WHERE （ SELECT COUNT（*） FROM test t2 WHERE t2.stat = t1.stat AND t1.createtime < t2.createtime ） < 3ORDER BY stat, createtime DESC--------------------------------------分割线 --------------------------------------Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm《MySQL权威指南（原书第2版）》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htmUbuntu 14.04 LTS 安装 LNMP NginxPHP5 （PHP-FPM）MySQL http://www.linuxidc.com/Linux/2014-05/102351.htmUbuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htmUbuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htmUbuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htmMySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm--------------------------------------分割线 --------------------------------------本文永久更新链接地址