mysql获取前30%的数据
2021/10/29 19:12:31
本文主要是介绍mysql获取前30%的数据,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1.在MySQL中,我们要查看前3条记录的话,是使用limit
mysql> SELECT * FROM kettle.r_step_attribute order by ID_STEP_ATTRIBUTE limit 3; +-------------------+-------------------+---------+------+---------------------+-----------+------------------------------------------------------+ | ID_STEP_ATTRIBUTE | ID_TRANSFORMATION | ID_STEP | NR | CODE | VALUE_NUM | VALUE_STR | +-------------------+-------------------+---------+------+---------------------+-----------+------------------------------------------------------+ | 1 | 1 | 1 | 0 | PARTITIONING_SCHEMA | 0 | NULL | | 2 | 1 | 1 | 0 | PARTITIONING_METHOD | 0 | none | | 3 | 1 | 1 | 0 | sql | 0 | SELECT <values> FROM <table name> WHERE <conditions> | +-------------------+-------------------+---------+------+---------------------+-----------+------------------------------------------------------+ 3 rows in set (0.00 sec) mysql>
2.简单的分页也可以使用力limit来完成
mysql> select * from kettle.r_step_attribute order by ID_STEP_ATTRIBUTE limit 3 offset 3; +-------------------+-------------------+---------+------+------------------+-----------+-----------+ | ID_STEP_ATTRIBUTE | ID_TRANSFORMATION | ID_STEP | NR | CODE | VALUE_NUM | VALUE_STR | +-------------------+-------------------+---------+------+------------------+-----------+-----------+ | 4 | 1 | 1 | 0 | limit | 0 | 0 | | 5 | 1 | 1 | 0 | lookup | 0 | NULL | | 6 | 1 | 1 | 0 | execute_each_row | 0 | N | +-------------------+-------------------+---------+------+------------------+-----------+-----------+ 3 rows in set (0.00 sec) mysql>
3.这都比较常见,如果我们想要获取数据的前30%呢?
在SQLServer中,我们可以直接使用top 30 percent 就可以了,但在MySQL中,我们得另想办法
前10%的数据,我们得知道数据一共有多少,那我们就知道该获取多少条记录了,然后还得知道记录的行号。
(1).获取行号
在MySQL中,获取行号,要使用变量(注:由于表中的数据比较多,此处我只拿10条记录来做实验)
select a.*, @row_num := @row_num + 1 as row_num from kettle.r_step_attribute a, (select @row_num := 0) b order by ID_STEP_ATTRIBUTE limit 10;
+-------------------+-------------------+---------+------+------------------------+-----------+------------------------------------------------------+---------+ | ID_STEP_ATTRIBUTE | ID_TRANSFORMATION | ID_STEP | NR | CODE | VALUE_NUM | VALUE_STR | row_num | +-------------------+-------------------+---------+------+------------------------+-----------+------------------------------------------------------+---------+ | 1 | 1 | 1 | 0 | PARTITIONING_SCHEMA | 0 | NULL | 1 | | 2 | 1 | 1 | 0 | PARTITIONING_METHOD | 0 | none | 2 | | 3 | 1 | 1 | 0 | sql | 0 | SELECT <values> FROM <table name> WHERE <conditions> | 3 | | 4 | 1 | 1 | 0 | limit | 0 | 0 | 4 | | 5 | 1 | 1 | 0 | lookup | 0 | NULL | 5 | | 6 | 1 | 1 | 0 | execute_each_row | 0 | N | 6 | | 7 | 1 | 1 | 0 | variables_active | 0 | N | 7 | | 8 | 1 | 1 | 0 | lazy_conversion_active | 0 | N | 8 | | 9 | 1 | 1 | 0 | cached_row_meta_active | 0 | N | 9 | | 10 | 1 | 1 | 0 | cluster_schema | 0 | NULL | 10 | +-------------------+-------------------+---------+------+------------------------+-----------+------------------------------------------------------+---------+ 10 rows in set (0.00 sec) mysql>
(2).前30%
有了行号,我们就可以根据行号和总记录数来过滤了
select * from (select a.*, @row_num := @row_num + 1 as row_num from kettle.r_step_attribute a, (select @row_num := 0) b order by ID_STEP_ATTRIBUTE limit 10) mm where mm.row_num <= (@row_num * 0.3);
+-------------------+-------------------+---------+------+---------------------+-----------+------------------------------------------------------+---------+ | ID_STEP_ATTRIBUTE | ID_TRANSFORMATION | ID_STEP | NR | CODE | VALUE_NUM | VALUE_STR | row_num | +-------------------+-------------------+---------+------+---------------------+-----------+------------------------------------------------------+---------+ | 1 | 1 | 1 | 0 | PARTITIONING_SCHEMA | 0 | NULL | 1 | | 2 | 1 | 1 | 0 | PARTITIONING_METHOD | 0 | none | 2 | | 3 | 1 | 1 | 0 | sql | 0 | SELECT <values> FROM <table name> WHERE <conditions> | 3 | +-------------------+-------------------+---------+------+---------------------+-----------+------------------------------------------------------+---------+ 3 rows in set (0.00 sec) mysql>
@row_num这个变量,是按行来的,所以我们再where条件中使用的时候,他是最后1条记录的行号,及总记录数
总记录数乘以30%,就是前30%的记录数,这样,我们就获取了前X%数据
参考链接:https://yuguiyang.github.io/2017/10/29/mysql-handbook-15/
这篇关于mysql获取前30%的数据的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享