Home > Database > Mysql Tutorial > MySQL使用自定义变量模拟分析函数

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-07 16:43:33
Original
1064 people have browsed it

MySQL使用自定义变量模拟分析函数,初始化实验结构和数据: create table test( tid varchar(32) primary key, stat int

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个记录的内容
 
这个需求似曾相识
 
但是这回是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
    ) t3

WHERE t3.rank  结果:

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

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template