这是一种基于模仿的方法row_number()，适用于不支持窗口功能的MySQL版本（计划将窗口功能包含在MySQL v8.x中）。

这种方法的结果将揭示有关最长序列的更多事实，而不仅仅是计数本身。有关详细信息，请参见下面的结果。

SQL小提琴

MySQL 5.6模式设置 ：

CREATE TABLE Table1
    (`lending_id` int, `installment_n` int, `status` varchar(15))
;

INSERT INTO Table1
    (`lending_id`, `installment_n`, `status`)
VALUES
    (71737, 1, 'PAID'),
    (71737, 2, 'PAID'),
    (71737, 3, 'PAID'),
    (71737, 4, 'PAID'),
    (71737, 5, 'PAID'),
    (71737, 6, 'WAITING_PAYMENT'),
    (71737, 7, 'WAITING_PAYMENT'),
    (71737, 8, 'WAITING_PAYMENT'),
    (71737, 9, 'WAITING_PAYMENT'),
    (71737, 10, 'WAITING_PAYMENT'),
    (71737, 11, 'WAITING_PAYMENT'),
    (71737, 12, 'WAITING_PAYMENT'),
    (71737, 13, 'WAITING_PAYMENT'),
    (71737, 14, 'WAITING_PAYMENT'),
    (71737, 15, 'WAITING_PAYMENT'),
    (71737, 16, 'WAITING_PAYMENT'),
    (71737, 17, 'WAITING_PAYMENT'),
    (71737, 18, 'WAITING_PAYMENT'),
    (71737, 19, 'WAITING_PAYMENT'),
    (71737, 20, 'WAITING_PAYMENT'),
    (71737, 21, 'WAITING_PAYMENT'),
    (354226, 1, 'PAID'),
    (354226, 2, 'PAID'),
    (354226, 3, 'WAITING_PAYMENT'),
    (354226, 4, 'WAITING_PAYMENT'),
    (354226, 5, 'WAITING_PAYMENT'),
    (354226, 6, 'WAITING_PAYMENT'),
    (354226, 7, 'PAID'),
    (354226, 8, 'WAITING_PAYMENT'),
    (354226, 9, 'WAITING_PAYMENT'),
    (354226, 10, 'WAITING_PAYMENT'),
    (354226, 11, 'WAITING_PAYMENT'),
    (354226, 12, 'WAITING_PAYMENT'),
    (354226, 13, 'WAITING_PAYMENT'),
    (354226, 14, 'WAITING_PAYMENT'),
    (354226, 15, 'WAITING_PAYMENT')
;

查询1 ：

select lending_id, status, start_at_inst, end_at_inst, inst_count
from (
      select IF(@prev_value=lending_id, @rn:=@rn+1 , @rn:=1) AS rn
            , lending_id, status, start_at_inst, end_at_inst, inst_count
            , @prev_value := lending_id z
      from (
           select lending_id
                   , status
                   , grpby
                   , min(installment_n) start_at_inst
                   , max(installment_n) end_at_inst
                   , (max(installment_n) + 1) - min(installment_n) inst_count
            from (
                 select
                        IF(@prev_value=concat_ws(',',lending_id,status), @rn:=@rn+1 , @rn:=1) AS rn
                      , t.*
                      , installment_n - @rn grpby
                      , @prev_value := concat_ws(',',lending_id,status) z
                 from Table1 t
                 cross join (
                     select @rn := 0 , @prev_value := ''
                     ) vars
                 order by lending_id, status,installment_n ASC
                 ) d1
            group by lending_id, status, grpby
          ) d2
      cross join (
          select @rn := 0 , @prev_value := ''
          ) vars
      order by lending_id, inst_count DESC
     ) d3
where rn = 1

结果 ：

| lending_id |          status | start_at_inst | end_at_inst | inst_count |
|------------|-----------------|---------------|-------------|------------|
|     354226 | WAITING_PAYMENT |             8 |          15 |          8 |
|      71737 | WAITING_PAYMENT |             6 |          21 |         16 |

在MySQL的V8.x正式发布之前，您不能使用row_number（）。但是对于已经支持db的用户，以及对于可用的MySQL用户，这是使用row_number（）的相同方法，我希望它比@variable方法更有效。

select
       lending_id, status, start_at_inst, end_at_inst, inst_count
from (
select 
       lending_id
       , status
       , grpby
       , min(installment_n) start_at_inst
       , max(installment_n) end_at_inst
       , (max(installment_n) + 1) - min(installment_n) inst_count
       , row_number() over(partition by lending_id order by (max(installment_n) + 1) - min(installment_n) DESC) rn
from (
     select
            t.*
          , installment_n - row_number() over(partition by lending_id, status order by installment_n) grpby
     from Table1 t
     ) d1
group by
       lending_id, status, grpby
    ) d2
where rn = 1
;

结果：

 lending_id | status          | start_at_inst | end_at_inst | inst_count
 ---------: | :-------------- | ------------: | ----------: | ---------:
      71737 | WAITING_PAYMENT |             6 |          21 |         16
     354226 | WAITING_PAYMENT |             8 |          15 |          8

dbfiddle（mariadb_10.2）
在这里