mysql实现开窗函数、Mysql实现分析函数
关键字：mysql实现开窗函数、Mysql实现分析函数、利用变量实现窗口函数
【Mysql5.7及以下版本】
适用范围：mysql5.7及以下版本，mysql8.0+ 可以直接使用窗口函数
注意，变量是从左到右顺序执行的
【测试数据】
— 测试数据
CREATE TABLE `tem` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`str` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (1, \’A\’);
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (2, \’B\’);
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (3, \’A\’);
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (4, \’C\’);
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (5, \’A\’);
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (6, \’C\’);
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (7, \’B\’);
【1】row_number() over(order by )
变量会最后再计算，所以是先排序好之后，才会开始计算@num
SELECT @num := @num+1 num, id, str FROM tem, (SELECT @str := \'\', @num := 0) t1 ORDER BY str, id;
【2】实现分组排名效果（row_number() over(partition by order by )）
--变量方式
SELECT @num := IF(@str = str, @num + 1, 1) num, id, @str := str str FROM tem, (SELECT @str := \'\', @num := 0) t1 ORDER BY str, id;
--子查询方式【取分组中前N行(排名前几名)】
mysql 相关子查询参考
select * from testGroup as a where a.ID in (select ID from testGroup b where a.UserID = b.UserID order by b.OrderID limit 2) --或者 select * from testGroup a where not exists (select 1 from testGroup b where a.UserID = b.UserID and a.OrderID > b.OrderID having count(1) >= 2) --或者 select * from testGroup a where (select count(1) from testGroup b where a.UserID = b.UserID and a.ID >= b.ID) <= 2 --没有唯一标识的表，可以用checksum来标识每行(MSSQL?) select * from testGroup as a where checksum(*) in (select top 2 checksum(*) from testGroup b where a.UserID = b.UserID order by b.OrderID)
mysql使用子查询实现
create table test1_1(id int auto_increment primary key,`subject` char(20),score int); insert into test1_1 values(null,\'语文\',99),(null,\'语文\',98),(null,\'语文\',97); insert into test1_1 values(null,\'数学\',89),(null,\'数学\',88),(null,\'数学\',87); insert into test1_1 values(null,\'英语\',79),(null,\'英语\',78),(null,\'英语\',77); -- 根据成绩，求出每个科目的前2名 select * from test1_1; select * from test1_1 t1 where (select count(1) from test1_1 t2 where t1.subject=t2.subject and t2.score>=t1.score ) <=2;
-- 查询结果【左边】原表内容 【右边】需求结果，根据成绩，求出每个科目的前2名
【3】实现dense_rank() over(order by)
--变量会最后再计算，所以是先排序好之后，才会开始计算@num
select id,@num:=IF(@STR=STR,@num,@num+1) rn,@str:=str str from tem t1,(select @str:=\'\',@num:=0) t2 order by str
--
—case when 形式，但该方法在mysql5.5中，只支持非0数字排序生成，字符会有大问题（任意字符被case when \’a\’ then else end，都会走else）
–且，赋值语句等于0时也为假
--错误的方式 select id,str, case when @str=str then @num when @str:=str then @num:=@num+1 end as \'rn\' from tem t1,(select @str:=\'\',@num:=1) t2 order by str
--正确的方式 select id,str, case when @str=str then @num when @str:=str then @num:=@num+1 else @num:=@num+1 end as \'rn\' from tem t1,(select @str:=\'\',@num:=0) t2 order by str
–关于数字的case when 验证
关于字符、字符串 的case when验证
【4】rank() over()
———————
测试数据代码大部分引用自：https://blog.csdn.net/mingqing6364/article/details/82621840
【5】Mysql8.0及以上，直接支持窗口函数
详细用法参考：https://blog.csdn.net/weixin_39010770/article/details/87862407