Mysql:一个接一个地查找具有相同值的行数 [英] Mysql: find number of rows which have same value one-after-other

问题描述

假设我有桌子

id|val
1 |1
2 |1
3 |2
4 |5
5 |2
6 |2
7 |2

如何获得这样的表格:

2 | 1 |2
3 | 2 |1
4 | 5 |1
7 | 2 |3

即表格，其中第三列是第二列中相似值的数量.

I.e table in which third column is number of similar values in second column.

当然我可以使用 php 或 perl 代码来做到这一点，但我记得只使用 sql 变量是可行的.

Sure i can do that using php or perl code, but i remmember it was posible by using sql variables only.

推荐答案

哦，我想我明白了.您关心相邻的值序列.第一列是最大id，第二列是值，第三列是长度.

Oh, I think I figured it out. You care about sequences of values that are adjacent. The first column is the maximum id, the second is the value, and the third is the length.

是的，你可以用变量来做到这一点:

Yes, you can do this with variables:

select max(id), val, count(*)
from (select t.*,
             (@grp := if(@v = val, @grp,
                         if(@v := val, @grp + 1, @grp + 1)
                        )
             ) as grp
      from yourtable t cross join
           (select @v := -1, @grp := -1) params
      order by id
     ) t
group by grp, val
order by max(id);

• 小提琴演示

这篇关于Mysql:一个接一个地查找具有相同值的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！