# mysql号段问题

create table t (a int primary key);

insert into t values (0);

insert into t values (1);

insert into t values (2);

insert into t values (10);

insert into t values (11);

insert into t values (12);

insert into t values (100);

mysql> select * from t;
+-----+
| a   |
+-----+
|   0 |
|   1 |
|   2 |
|  10 |
|  11 |
|  12 |
| 100 |
+-----+
7 rows in set (0.00 sec)

select min(a), max(a)
from
(select a, rn, a - rn as diff from
(select a, @a := @a + 1 rn from t, (select @a := 0) as a)
as b) as c
group by diff;

mysql> select min(a), max(a)  --已知号码求号段
-> from
-> (select a, rn, a - rn as diff from
-> (select a, @a := @a + 1 rn from t, (select @a := 0) as a)
-> as b) as c
-> group by diff;
+--------+--------+
| min(a) | max(a) |
+--------+--------+
|      0 |      2 |
|     10 |     12 |
|    100 |    100 |
+--------+--------+
3 rows in set (0.01 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from t;
+-------+------+
| start | end  |
+-------+------+
|     1 |    5 |
|   100 |  103 |
+-------+------+
2 rows in set (0.01 sec)

select rn, start, end, start + rn - 1 from  --已知号段求号码
(select @a := @a + 1 rn from big_table, ((select @a:=0) as b) where @a < (select max(end - start) from t) ) as tmp, t
where t.end >= t.start + tmp.rn - 1
order by 4;

+------+-------+------+----------------+
| rn   | start | end  | start + rn - 1 |
+------+-------+------+----------------+
|    1 |     1 |    5 |              1 |
|    2 |     1 |    5 |              2 |
|    3 |     1 |    5 |              3 |
|    4 |     1 |    5 |              4 |
|    1 |   100 |  103 |            100 |
|    2 |   100 |  103 |            101 |
|    3 |   100 |  103 |            102 |
|    4 |   100 |  103 |            103 |
+------+-------+------+----------------+
8 rows in set (0.00 sec)

• 博文量
55
• 访问量
78155