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/