数据库|mysql教程
MySQL自定义变量,MySQL分析函数,MySQL使用自定
数据库-mysql教程
ftp c源码,vscode替换换行符,ubuntu读取vmdk,tomcat 无会话模式,爬虫市场图纸,php校园招聘,黑帽seo 快速排名外推,dede5.7微电影网站模板lzw
MySQL使用自定义变量模拟分析函数，初始化实验结构和数据： create table test( tid varchar(32) primary key, stat int
c webservice 源码,vscode定义快捷键,ubuntu网络ip,网页找不到tomcat,拉钩 爬虫 企业,php获取登录ip,sem和seo关系式,博彩app网站源码,dedecms织梦新闻资讯模板lzw
软件远程管理源码,ubuntu截屏命令,tomcat怎么加环境变量,go爬虫301,php实现了0的阶乘,鼓楼seo公司lzw
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,’-08-20 10:01:09′);
INSERT INTO test (tid,stat,createtime) VALUES (‘dc9a4438e577f4b08f7033a305544d47′,-1,’-08-20 10:00:19’);
INSERT INTO test (tid,stat,createtime) VALUES (‘23055228532bbba5a68d6ada11bcf33f’,-1,’-08-20 09:58:32′);
INSERT INTO test (tid,stat,createtime) VALUES (‘5711ee1610d07a55e64c7948667de6e8′,-1,’-08-20 09:58:09’);
INSERT INTO test (tid,stat,createtime) VALUES (‘035e06d8afd681a9904bd74e9860f8cb’,-1,’-08-20 09:57:52′);
INSERT INTO test (tid,stat,createtime) VALUES (‘3890efc08f37fa489a4e130cb04f71ac’,-1,’-08-20 09:57:48′);
INSERT INTO test (tid,stat,createtime) VALUES (‘1b6ed9db663dae470b45c722a61d08b0′,-1,’-08-20 09:56:40’);
INSERT INTO test (tid,stat,createtime) VALUES (‘8fb3409015e6b2cf85ba6ee90f15b58f’,-1,’-08-20 09:54:40′);
INSERT INTO test (tid,stat,createtime) VALUES (‘0badb1f4c2b1a89f1c473b992183add3′,-1,’-08-20 09:54:33’);
INSERT INTO test (tid,stat,createtime) VALUES (’89b8af5eb473b2d4f50dd9e10773a9cc’,-1,’-08-20 09:53:54′);
INSERT INTO test (tid,stat,createtime) VALUES (‘77923a7397110224b5f94e7d0bd297de’,2,’-08-19 17:13:17′);
INSERT INTO test (tid,stat,createtime) VALUES (‘0df1da77cfdbe64edcd4d645197174af’,2,’-08-19 12:20:21′);
INSERT INTO test (tid,stat,createtime) VALUES (’43daef6bfbc46dbfdbb97e74173dab30′,2,’-08-19 09:54:08′);
INSERT INTO test (tid,stat,createtime) VALUES (‘d5d12c510391314f48054c6c9ab9535c’,2,’-08-19 09:23:41′);
INSERT INTO test (tid,stat,createtime) VALUES (‘f7c123143752498b7c9a226a9583ae49′,2,’-08-19 01:14:21’);
INSERT INTO test (tid,stat,createtime) VALUES (‘da6a9a78897a42ae0a565cd0fabd76bb’,2,’-08-18 21:59:46′);
INSERT INTO test (tid,stat,createtime) VALUES (‘9cd3f83ab04120504a880523702491d7′,2,’-08-18 16:26:30’);
INSERT INTO test (tid,stat,createtime) VALUES (‘4dfa129ba64e7062afa37e56bb9632de’,2,’-08-18 14:32:41′);
INSERT INTO test (tid,stat,createtime) VALUES (‘a9a731870e1c02278c22ce1ab36fa43c’,2,’-08-18 14:31:26′);
INSERT INTO test (tid,stat,createtime) VALUES (’97f39d2a1e519f99e602e72cfc45fe0c’,2,’-08-17 11:47:52′);
INSERT INTO test (tid,stat,createtime) VALUES (’31ba95265a96971221ddf9320c79eed8′,3,’-08-20 02:08:50′);
INSERT INTO test (tid,stat,createtime) VALUES (‘060d92222edcb6f583cb4cd0244aadc0′,3,’-08-20 02:05:54’);
INSERT INTO test (tid,stat,createtime) VALUES (‘7d3eb4ea06b08e961b9fe7726fd4′,3,’-08-20 02:00:11’);
INSERT INTO test (tid,stat,createtime) VALUES (‘c633bc16cb8c3bb4ffa7f00682701b92′,3,’-08-20 01:54:22’);
INSERT INTO test (tid,stat,createtime) VALUES (‘e43bb7e7274259712b389e3feabc068f’,3,’-08-20 01:49:36′);
INSERT INTO test (tid,stat,createtime) VALUES (‘bdabf3d80fb097222112cb30cdc48117′,3,’-08-20 01:48:48’);
INSERT INTO test (tid,stat,createtime) VALUES (‘170e2bdc11d517a56b7ce23d85633e42′,3,’-08-20 01:46:56’);
INSERT INTO test (tid,stat,createtime) VALUES (‘7e79f6065ae8bb215cee43a4efbcd852′,3,’-08-20 01:44:17’);
INSERT INTO test (tid,stat,createtime) VALUES (‘04728676e3305de05a18333ddfc76c01′,3,’-08-20 01:39:05’);
INSERT INTO test (tid,stat,createtime) VALUES (‘d987176d350d4fefcc92b9a7ebb4f288′,3,’-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 <= 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
