场景:有1张用户表,需查找出登陆次数前30%的用户
表信息如下:
CREATE TABLE `sys_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
`login_num` int DEFAULT NULL COMMENT '登录次数',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1888 DEFAULT CHARSET=utf8mb3 COMMENT='用户信息表';
部分数据:
思路:(1)先对登陆次数进行降序排序
此时需用到函数:row_number() over(),该函数能对字段进行自增排序后获取最大排序值
select *,
row_number() over(order by login_num desc) as sort
from sys_user;
思路:(2)获取排序最大值,此时该值为该列的总数量
利用子查询,直接把上面的查询语句套进行:
SELECT max(sort)
FROM (select *,
row_number() over(order by login_num desc) as sort
from sys_user) a;
思路:(3)利用四则运算获取前30%的用户信息
利用子查询,直接把思路(1)的查询语句套进行:
select *
from (select *,
row_number() over(order by login_num desc) as sort
from sys_user) a
where sort <= 74 * 0.3;