SQL--计算某一值连续出现的最大次数

[email protected] : test > select * from itpub1;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
| 6 | 2 |
| 7 | 1 |
| 8 | 1 |
+------+------+
8 rows in set (0.00 sec)

[email protected] : test > select aa.name,max(rownum) maxrow from ( select ib.id,ib.name,if((ib.name=@pname) , @row:=@row+1 , @row:=1) rownum , @pname:=ib.name pn from itpub1 ib, (select @row:=0, @pname:='000000') row1 ) aa group by aa.name;
+------+--------+
| name | maxrow |
+------+--------+
| 1 | 4 |
| 2 | 2 |
+------+--------+

2 rows in set (0.00 sec)