mysql实现开窗函数、Mysql实现分析函数

关键字:mysql实现开窗函数、Mysql实现分析函数、利用变量实现窗口函数

适用范围: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;

mysql实现开窗函数、Mysql实现分析函数


【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;

mysql实现开窗函数、Mysql实现分析函数


--子查询方式【取分组中前N行(排名前几名)】
mysql实现开窗函数、Mysql实现分析函数
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名

mysql实现开窗函数、Mysql实现分析函数 mysql实现开窗函数、Mysql实现分析函数


mysql实现开窗函数、Mysql实现分析函数


【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
--

mysql实现开窗函数、Mysql实现分析函数

---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
  

  mysql实现开窗函数、Mysql实现分析函数

--正确的方式
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   mysql实现开窗函数、Mysql实现分析函数
 

--关于数字的case when 验证

  mysql实现开窗函数、Mysql实现分析函数

关于字符、字符串 的case when验证
  

  mysql实现开窗函数、Mysql实现分析函数


【4】rank() over()

  mysql实现开窗函数、Mysql实现分析函数

  

---------------------
测试数据代码大部分引用自:https://blog.csdn.net/mingqing6364/article/details/82621840

上一篇:智能存储解决方案需求为啥高?


下一篇:Study 8 —— 数据类型(列表/list)