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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25361369/viewspace-722731/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25361369/viewspace-722731/