我的数据如如有
id num
1 1001
2 1001
3 1001
4 1001
5 1002
6 1002
7 1001
8 1001
9 1002
我想实现的语句子是连续的间隔数,上面这种的结果要
counts 1001
连续4次
1002连续2次
1001连续2次
请问MYSQL要怎么写,谢谢求解 ! MySQL
id num
1 1001
2 1001
3 1001
4 1001
5 1002
6 1002
7 1001
8 1001
9 1002
我想实现的语句子是连续的间隔数,上面这种的结果要
counts 1001
连续4次
1002连续2次
1001连续2次
请问MYSQL要怎么写,谢谢求解 ! MySQL
SET @b=0;
SET @c=0;
SELECT num,bz,MAX(js) FROM (
SELECT *,@a:=IF(@b=num,@a+1,1) AS js,@c:=IF(@b=num,@c,@c+1) AS bz,@b:=num FROM tt2) d
GROUP BY num,bz ORDER BY 2,1
1 1001
2 1001
3 1001
4 1001
5 1002
6 1002
7 1001
8 1001
9 1002
[root@192 ~]# awk '{print $2}' test.txt | uniq -c
4 1001
2 1002
2 1001
1 1002
[root@192 ~]#
+----+------+
| id | num |
+----+------+
| 1 | 1001 |
| 2 | 1001 |
| 3 | 1001 |
| 4 | 1001 |
| 5 | 1002 |
| 6 | 1002 |
| 7 | 1001 |
| 8 | 1001 |
| 9 | 1002 |
+----+------+
9 rows in set (0.00 sec)mysql> select num,
-> concat('连续',count(*) ,'次 从', min(sid) ,'至',eid) as cnt
-> from (
-> select a.id as sid,max(b.id) as eid,a.num
-> from table1 a , table1 b
-> where a.id<=b.id
-> and a.num=b.num
-> and not exists (select 1 from table1 where id between a.id and b.id and num!=a.num)
-> group by a.id
-> ) t
-> group by eid;
+------+----------------+
| num | cnt |
+------+----------------+
| 1001 | 连续4次 从1至4 |
| 1002 | 连续2次 从5至6 |
| 1001 | 连续2次 从7至8 |
| 1002 | 连续1次 从9至9 |
+------+----------------+
4 rows in set (0.00 sec)mysql>
-> group by a.id
-> ) t这一段什么意思?