Mysql求最新连续打卡天数

drop table if exists `t`;
create table `t`(
    `id` int unsigned auto_increment, 
    `uid` int unsigned, 
    `b` int unsigned, 
    `c` int unsigned, 
    primary key(`id`)
);
 
insert into `t`(`uid`, `b`, `c`) values(1, 1, 1);
insert into `t`(`uid`, `b`, `c`) values(2, 1, 1);
insert into `t`(`uid`, `b`, `c`) values(3, 1, 1);
insert into `t`(`uid`, `b`, `c`) values(1, 2, 1);
insert into `t`(`uid`, `b`, `c`) values(2, 2, 1);
insert into `t`(`uid`, `b`, `c`) values(3, 2, 1);
insert into `t`(`uid`, `b`, `c`) values(1, 3, 0);
insert into `t`(`uid`, `b`, `c`) values(2, 3, 1);
insert into `t`(`uid`, `b`, `c`) values(3, 3, 0);
insert into `t`(`uid`, `b`, `c`) values(1, 4, 1);
insert into `t`(`uid`, `b`, `c`) values(2, 4, 0);
insert into `t`(`uid`, `b`, `c`) values(3, 4, 1);
insert into `t`(`uid`, `b`, `c`) values(1, 5, 1);
insert into `t`(`uid`, `b`, `c`) values(2, 5, 0);
insert into `t`(`uid`, `b`, `c`) values(3, 5, 1);
insert into `t`(`uid`, `b`, `c`) values(1, 6, 1);
insert into `t`(`uid`, `b`, `c`) values(2, 6, 1);
insert into `t`(`uid`, `b`, `c`) values(3, 6, 1);
insert into `t`(`uid`, `b`, `c`) values(1, 7, 1);
insert into `t`(`uid`, `b`, `c`) values(2, 7, 1);
insert into `t`(`uid`, `b`, `c`) values(3, 7, 1);

需求：
求各个UID的c最新连续=1的次数
最后结果是
uid b num
1 7 4次
2 7 2次
3 7 4次

SELECT
  t.uid,
  MAX(t.b) AS b,
  MAX(t.b) - MAX(r.max) AS last_b
FROM
  `t`
  JOIN
    (SELECT
      uid,
      MAX(b) AS `max`
    FROM
      `t`
    WHERE c = 0
    GROUP BY uid) AS r ON r.uid = t.uid
WHERE t.c = 1
GROUP BY t.uid

image.png