MySQL-返回在匹配条件之后具有n个连续记录的所有记录

user1830274：

MySQL v5.5

嗨-我需要从一个简单的表中创建一个选择查询，在该表中，它返回所有具有一定数量连续行且标记为可用的行。

表格数据示例如下：

id   Slot    Available
0    5.00pm  1
1    5.10pm  1   
2    5.20pm  1
3    5.30pm  0
4    5.40pm  1
5    5.50pm  1
6    6.00pm  1
7    6.10pm  1

我有一个变量，它将确定所需的插槽数。

因此，例如，如果变量为3，则我需要结果返回具有2个连续插槽的所有插槽。

在此示例中期望的结果将是：

id   Slot    Available
0    5.00pm  1
4    5.40pm  1
5    5.50pm  1

任何帮助将不胜感激。

（我不是专业开发人员，所以请保持谦虚:)）

非常感谢

zedfoxus：

我敢肯定，有一种方法可以用SQL完成您想要的事情。我有一个可以使用的存储过程示例。

DELIMITER //

DROP PROCEDURE IF EXISTS GetTimeSlots;

CREATE PROCEDURE GetTimeSlots(NoOfSlots int)
BEGIN

    declare finished bool default false;

    -- stores id, slot and available from the cursor
    declare v_id int;
    declare v_slot text;
    declare v_available int;

    -- stores how many slots were found consecutively
    declare v_slotsfound int;

    -- loop through all available records
    declare c cursor for
    select id, slot, available from test where available = 1;

    declare continue handler for not found
    set finished = true;

    -- create empty table to store slots we find
    drop temporary table if exists tmp;
    create temporary table tmp
    select * from test where 1=2;

    open c;
    looper: loop
        fetch c into v_id, v_slot, v_available;
        if finished then
            leave looper;
        end if;

        select sum(case
                   when available then 1
                   else 0
                   end) into v_slotsfound
        from (
            -- list out as many slots you need
            -- outer query will total up the # of available slots
            select available from test where id >= v_id
            order by id
            limit NoOfSlots
        ) t;

        -- if # of available slots equals how many you want, store data
        -- in temporary table
        if v_slotsfound = NoOfSlots then
            insert into tmp values (v_id, v_slot, v_available);
        end if;

    end loop looper;
    close c;

    -- output the results
    select * from tmp;
    drop temporary table if exists tmp;

END //

DELIMITER ;

运行

mysql> call gettimeslots(3);
+------+--------+-----------+
| id   | slot   | available |
+------+--------+-----------+
|    0 | 5.00pm |         1 |
|    4 | 5.40pm |         1 |
|    5 | 5.50pm |         1 |
+------+--------+-----------+

如果要连续2个插槽：

mysql> call gettimeslots(2);
+------+--------+-----------+
| id   | slot   | available |
+------+--------+-----------+
|    0 | 5.00pm |         1 |
|    1 | 5.10pm |         1 |
|    4 | 5.40pm |         1 |
|    5 | 5.50pm |         1 |
|    6 | 6.00pm |         1 |
+------+--------+-----------+

如果要连续4个广告位：

mysql> call gettimeslots(4);
+------+--------+-----------+
| id   | slot   | available |
+------+--------+-----------+
|    4 | 5.40pm |         1 |
+------+--------+-----------+

使用数据

create table test (id int, slot text, avaialable int);
insert into test values
(0,    '5.00pm', 1)
,(1,    '5.10pm',  1)   
,(2,    '5.20pm',  1)
,(3,    '5.30pm',  0)
,(4,    '5.40pm',  1)
,(5,    '5.50pm',  1)
,(6,    '6.00pm',  1)
,(7,    '6.10pm',  1);

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。