有一需求，赐教各位大神

[一把编程的菜刀]

数据库 a
字段 uid，b， c
其值如下：
uid b c
1 1 1
2 1 1
3 1 1
1 2 1
2 2 1
3 2 1
1 3 0
2 3 1
3 3 0
1 4 1
2 4 0
3 4 1
1 5 1
2 5 0
3 5 1
1 6 1
2 6 1
3 6 1
1 7 1
2 7 1
3 7 1

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

[yanglinqiang]

select a.uid,count(1) from t a,(select uid,max(b) as b from t where c=0 group by uid) b where a.uid=b.uid and a.b>b.b group by a.uid;

[可怜的小白兔]

看不懂你的需求

[qq_38865092]

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

[qq_38865092]

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

[Mr_heber]

楼主是在做类似连续签到的业务需求吗

[青鬆下的坚躯]

看上这400分了

#EXPLAIN 
SELECT 
	uid, 
	MAX(b) AS b, 
	POSITION(0 IN GROUP_CONCAT(c ORDER BY b DESC SEPARATOR ""))-1 AS num 
FROM t 
GROUP BY t.uid

+------+------+------+
| uid  | b    | num  |
+------+------+------+
|    1 |    7 |    4 |
|    2 |    7 |    2 |
|    3 |    7 |    4 |
+------+------+------+
3 rows in set (0.00 sec)

======================================== 256内存，单CPU，VBox虚拟机实验 -> 120W条记录查询示例如下：

MariaDB [test]> select count(*) from t;
+----------+
| count(*) |
+----------+
|  1200018 |
+----------+
1 row in set (0.34 sec)
MariaDB [test]> SELECT uid, MAX(b) AS b, POSITION(0 IN GROUP_CONCAT(c ORDER BY b DESC SEPARATOR ""))-1 AS num FROM t GROUP BY t.uid;
+------+--------+------+
| uid  | b      | num  |
+------+--------+------+
|    1 | 400006 |    0 |
|    2 | 400006 |    0 |
|    3 | 400006 |    0 |
+------+--------+------+
3 rows in set (0.87 sec)

模拟数据生成方式

#创建存储过程
CREATE PROCEDURE test_insert () 
    #开始
    BEGIN
        #定义变量 
        DECLARE i INT DEFAULT 1;
        DECLARE b INT DEFAULT 8;
            #条件判断
            WHILE i<400000 #400000*3=1200000
            #执行
            DO 
                #SQL
                INSERT INTO t(uid, b, c) VALUES
                (1, b, FLOOR(RAND()*10%2)),
                (2, b, FLOOR(RAND()*10%2)),
                (3, b, FLOOR(RAND()*10%2));
                #变量增加
                SET i=i+1;
                SET b=b+1;
            #结束循环 
            END WHILE ;
        #提交 
        commit; 
    #结束
    END;
#执行
CALL test_insert();    
#删除存储过程
drop procedure test_insert ;

[evanweng]

先加个自增id
select t1.uid, count(1) from t t1 where t1.c = 1 and t1.id > (
select max(t2.id) from t t2 where t2.uid = t1.uid and t2.c = 0
) group by t1.uid

[evanweng]

引用 75 楼 evanweng 的回复:

先加个自增id
select t1.uid, count(1) from t t1 where t1.c = 1 and t1.id > (
select max(t2.id) from t t2 where t2.uid = t1.uid and t2.c = 0
) group by t1.uid

数据量大的话，给uid和c加索引

[evanweng]

引用 76 楼 evanweng 的回复:

[quote=引用 75 楼 evanweng 的回复:]
先加个自增id
select t1.uid, count(1) from t t1 where t1.c = 1 and t1.id > (
select max(t2.id) from t t2 where t2.uid = t1.uid and t2.c = 0
) group by t1.uid

数据量大的话，给uid和c加索引[/quote]上面那个有点慢，改了一下，十万条数据，不加索引，100毫秒左右
select t1.uid, count(1) from t t1,
(select t2.uid, max(t2.id) id from t t2 where t2.c = 0 group by t2.uid) t3
where t1.c = 1 and t1.uid = t3.uid and t1.id > t3.id group by t1.uid

[evanweng]

引用 77 楼 evanweng 的回复:

[quote=引用 76 楼 evanweng 的回复:]
[quote=引用 75 楼 evanweng 的回复:]
先加个自增id
select t1.uid, count(1) from t t1 where t1.c = 1 and t1.id > (
select max(t2.id) from t t2 where t2.uid = t1.uid and t2.c = 0
) group by t1.uid

数据量大的话，给uid和c加索引[/quote]上面那个有点慢，改了一下，十万条数据，不加索引，100毫秒左右
select t1.uid, count(1) from t t1,
(select t2.uid, max(t2.id) id from t t2 where t2.c = 0 group by t2.uid) t3
where t1.c = 1 and t1.uid = t3.uid and t1.id > t3.id group by t1.uid[/quote]再考虑c=0不存在的情况，加个左连接，完美！
select t1.uid, count(1) from t t1
left join (select t2.uid, max(t2.id) id from t t2 where t2.c = 0 group by t2.uid) t3 on t1.uid = t3.uid
where t1.c = 1 and t1.id > ifnull(t3.id,0) group by t1.uid

[XM3990205HZ2552]

接分 我一点分都没有了，给点分我吧

[huangfuyk]

可以可以，学到了。

[有戏说戏]

select uid,max(b) as b,LOCATE(0,REVERSE(REPLACE(GROUP_CONCAT(c order by b),',','')))-1 as s from a group by uid ORDER BY b

思路，把c字段按b排序拼接，翻转后找见0第一次出现的位置-1，就是最新的长度

[苍穹0113]

发言被禁了吗？

[美奇开发工作室]

你这是要计算长龙吧，彩票方面的知识。

[美奇开发工作室]

如果你是要计算长龙，你直接去看别人源码里的实现方式就好了，数据库设计、数据层、业务层，展示层都一清二楚，比你这般在论坛上瞎子摸象要省事多了。

[giào！ giào ！]

rownumber

[惊喜不断]

这么多年了，还没结帖呢， 这种一看数据就非常庞大，换种思路试试，尝试用触发器每次增加数据即计算一下结果，非常简单。

[惊喜不断]

触发器技术，一般web攻城狮 ，都不知道有这东西，也是无语.........

[༺七喜༻]

$Tb->field('cast(c as UNSIGNED INTEGER) as c,count(*)')->group('uid')->order("c desc")->select();