计算行数直到列中的值更改 mysql [英] Count rows until value in column changes mysql

问题描述

所以我有一个表，其中一列具有真或假(0 或 1)值.我想知道在我击中 1 之前连续 0 的计数.例如，如果我有以下时间 ASC:

So I have a table with a column that has a true or false (0 or 1) value. I want to know the count of consecutive 0s before I hit a 1. So for example if I had the following time ASC:

0
0
1
0
1
0
0
0

最后一个连续 0 的返回数将是 3.我一直在寻找一种计数方法，直到值发生变化，但到目前为止还没有运气.有什么想法吗?

The return number of last consecutive 0s would be 3. I've been looking for a way to count until the value changes but so far no luck. Any thoughts?

这是我的表的一个更清晰的例子

here's a clearer example of my table

|id|counter|user_id|
--------------------
|0 |0      |3      | 
--------------------
|1 |0      |3      | 
--------------------
|2 |1      |3      | 
--------------------
|3 |0      |3      | 
--------------------
|4 |1      |3      | 
--------------------
|5 |0      |3      | 
--------------------
|6 |1      |8      | 
--------------------
|7 |0      |3      | 
--------------------
|8 |0      |3      | 
--------------------

user_id = 3，结果为 3

for user_id = 3, the result would be 3

推荐答案

您的 time 列似乎指定了顺序.那么末尾的零数将是:

Your time column seems to specify the ordering. Then the number of zeros at the end would be:

select count(*)
from table t
where t.col = 0 and
      t.time > (select max(t2.time) from table t2 where t2.col = 1);

这篇关于计算行数直到列中的值更改 mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！