mysql 取得表中重复值之间的间隔以及最大间隔

今天在论坛上看到一个帖子，我觉得挺有意思，帖子如下：

虽说我现在在mysql方面能力有限，但也想尝试一把，想把它搞出来，经过我的不懈努力，算是有个结果了，可以给自己一个交代了，

鉴于目前的我是个mysql菜鸟，看着回帖子的都是版主，大拿级人物，没敢和人家平起平坐，所以我没把自己的小小成果以帖子的形式回给人家，还是放到自己小窝里供大家和自己分享吧

以下为我做的实验步骤：

[root@pw2 tmp]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.66 Source distribution


Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test(id varchar(10));
Query OK, 0 rows affected (0.04 sec)


mysql> insert into test(id) values('a');
Query OK, 1 row affected (0.00 sec)


mysql> insert into test(id) values('b');
Query OK, 1 row affected (0.00 sec)


mysql> insert into test(id) values('c');
Query OK, 1 row affected (0.00 sec)


mysql> insert into test(id) values('a');
Query OK, 1 row affected (0.00 sec)


mysql> insert into test(id) values('d');
Query OK, 1 row affected (0.00 sec)


mysql> insert into test(id) values('e');
Query OK, 1 row affected (0.00 sec)


mysql> insert into test(id) values('f');
Query OK, 1 row affected (0.00 sec)

.
.
.

mysql> insert into test(id) values('o');
Query OK, 1 row affected (0.00 sec)


mysql> insert into test(id) values('a');
Query OK, 1 row affected (0.00 sec)


mysql> select * from test;
+------+
| id   |
+------+
| a    |
| b    |
| c    |
| a    |
| d    |
| e    |
| f    |
| a    |
| g    |
| h    |
| i    |
| a    |
| j    |
| k    |
| l    |
| m    |
| n    |
| o    |
| a    |
+------+
19 rows in set (0.00 sec)


mysql> set @rownum=0;                                   --初始化变量rownum，初始值为0
Query OK, 0 rows affected (0.00 sec)


mysql> set @rownum1=0;                                 --初始化变量rownum1，初始值为0
Query OK, 0 rows affected (0.00 sec)

--查询表中重复记录a的各个间隔
mysql> select c.rownum,c.id,min(c.rownum-d.rownum)-1 from (select b.rownum,b.id from (select @rownum:=@rownum+1 as rownum,id from test) b where b.id='a') c
    -> inner join (select e.rownum,e.id from (select @rownum1:=@rownum1+1 as rownum,id from test) e where e.id='a') d on (c.rownum>d.rownum)
    -> group by c.rownum,c.id;
+--------+------+--------------------------+
| rownum | id   | min(c.rownum-d.rownum)-1 |
+--------+------+--------------------------+
|      4 | a    |                        2 |
|      8 | a    |                        3 |
|     12 | a    |                        3 |
|     19 | a    |                        6 |
+--------+------+--------------------------+
4 rows in set (0.00 sec)

--查询表中重复记录a的最大间隔

mysql> select max(f.z) from (select min(c.rownum-d.rownum)-1  z from (select b.rownum,b.id from (select @rownum:=@rownum+1 as rownum,id from test) b where b.id='a') c
    -> inner join (select e.rownum,e.id from (select @rownum1:=@rownum1+1 as rownum,id from test) e where e.id='a') d on (c.rownum>d.rownum)
    -> group by c.rownum,c.id) f;
+----------+
| max(f.z) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)


mysql> 

解释说明：

要是只是按帖子中说的一个字段，无法取得重复记录之间的间隔，同时也就不能求出最大间隔

所以，需要构造一个虚拟列（rownum），用这列记录表中记录的行号。

设置两个临时变量，初始值设置为0: set @rownum=0;   以及 set @rownum1=0;

让@rownum自加1并赋值给自己作为行号

min(c.rownum-d.rownum)  为的是让相邻两个重复值之间相减

min(c.rownum-d.rownum)-1  取得间隔

max(f.z) 取得最大间隔

语句看起来挺复杂，其实就是做了个自连接