MySQL 过滤步骤

Question

我有一张 table

create table
(
 col1 int,
 nr   int
);

insert into table (col1, nr) values (1, 1);
insert into table (col1, nr) values (2, 1);
insert into table (col1, nr) values (3, 1);

insert into table (col1, nr) values (10, 2);
insert into table (col1, nr) values (208, 2);

insert into table (col1, nr) values (356, 3);

... 

insert into table (col1, nr) values (43, 10000);
insert into table (col1, nr) values (34, 10000);

根据此示例，max(nr) = 10000。如何进行查询以按以下方式过滤上表中的数据:

count on (col1) for nr between 1 and 20, the 20 is the step
count on (col1) for nr between 21 and 40
count on (col1) for nr between 41 and 60
...
count on (col1) for nr between X and 10000

这就是我需要的，谢谢:

select 
rownum,
((rownum -1) * 100) + 1 as p_nr_token_start,
rownum * 100 as p_nr_token_end
from (
select 
 @rn:=@rn+1 as rownum
from information_schema.columns a,
(select @rn:=0) b) rr
where (rownum * 100) <= 100000

Answer 1

您可以执行以下操作:

select
  sum(case when nr between 1 and 20 then 1 else 0 end) as step_1_20,
  sum(case when nr between 21 and 40 then 1 else 0 end) as step_21_40,
  sum(case when nr between 41 and 60 then 1 else 0 end) as step_41_60,
  ...
  sum(case when nr between 9981 and 10000 then 1 else 0 end) as step_9981_10000
from table

示例:http://sqlfiddle.com/#!9/343a1/2

您可以使用存储过程来创建动态 SQL 查询并执行它，而不是手动创建它。准备动态语句的示例如下:https://irhowto.wordpress.com/2012/10/29/mysql-stored-procedure-prepared-statements-and-sql-injection/

编辑: 分隔符//

create procedure get_data()
begin
    declare max_number int default 10000;
    declare number_counter int default 0;
    set @s = 'select ';
    while number_counter < max_number do
        set @s = concat(@s,
            case when number_counter = 0 then '' else ',' end,
            'sum(case when nr between ', number_counter + 1,
            ' and ', number_counter + 20, ' then 1 else 0 end) '
            ' as step_', number_counter + 1,
            '_', number_counter + 20);
        set number_counter = number_counter + 20;
    end while;
    set @s = concat(@s, ' from test');
    prepare stmt from @s;
    execute stmt;
    deallocate prepare stmt;
end //

delimiter ;

call get_data();

这个存储过程将为您提供您想要的。您将获得 500 列，这可能非常不实用。