ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql号段问题

mysql号段问题

原创 Linux操作系统 作者:tyoodz 时间:2012-05-04 16:07:36 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Python模拟抽象类
下一篇: Oracle number解析
请登录后发表评论 登录
全部评论

注册时间:2011-05-16

  • 博文量
    55
  • 访问量
    71952