Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2880566
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2012-05-03 10:50:40

在老杨的博客上看到一个“号码分段的问题”,
原文地址:
 

需求是找到一组连续ID的最小值和最大值,要求除了数值连续外,另外两个字段也必须相同,否则认为是新的号段。

简单的说,最终需要的结果类似:

100-101 11 S
102-104 11 N
105-107 00 S
108-110 00 N
... ...
200-201 11 S
202-204 11 N
205-207 00 S
208-210 00 N

 
老杨用的是LAG分析函数。
 
 
然后自己也写了一个,用的是ROW_NUMBER分析函数及其GROUP BY:
 

SQL> DESC T
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 CA                                                 NUMBER(38)
 REG                                                CHAR(1)
SQL> SELECT * FROM T;
        ID         CA RE
---------- ---------- --
       100         11 S
       101         11 S
       102         11 N
       103         11 N
       104         11 N
       105          0 S
       106          0 S
       107          0 S
       108          0 N
       109          0 N
       110          0 N
        ID         CA RE
---------- ---------- --
       111         11 S
       112         11 S
       113         11 N
       114         11 N
       115         11 N
       116          0 S
       117          0 S
       118          0 N
       119          0 N
       120          0 N
       121         11 S
        ID         CA RE
---------- ---------- --
       122         11 S
       123         11 S
       124         11 S
       125         11 S
       126         11 S
       127         11 S
       128         11 S
       129         11 S
       130         11 S
       200         11 S
       201         11 S
        ID         CA RE
---------- ---------- --
       202         11 N
       203         11 N
       204         11 N
       205          0 S
       206          0 S
       207          0 S
       208          0 N
       209          0 N
       210          0 N
       211         11 S
       212         11 S
        ID         CA RE
---------- ---------- --
       213         11 N
       214         11 N
       215         11 N
       300         11 S
       301         11 S
       302         11 N
       303         11 N
       304         11 N
       305          0 S
       306          0 S
       307          0 S
        ID         CA RE
---------- ---------- --
       308          0 N
       309          0 N
       310          0 N
       311         11 S
       312         11 S
       313         11 N
       314         11 N
       315         11 N
 
已选择63行。
 
SQL> COL ID_RANGE FORMAT A20
SQL> SELECT  TO_CHAR(MIN(ID))||'-'||TO_CHAR(MAX(ID)) ID_RANGE,CA,REG
  2  FROM
  3  (
  4  SELECT CA,REG,ID,ID-ROW_NUMBER() OVER (PARTITION BY CA,REG ORDER BY ID) RN
  5  FROM T
  6  )
  7  GROUP BY CA,REG,RN
  8  ORDER BY 1
  9  /
ID_RANGE                     CA RE
-------------------- ---------- --
100-101                      11 S
102-104                      11 N
105-107                       0 S
108-110                       0 N
111-112                      11 S
113-115                      11 N
116-117                       0 S
118-120                       0 N
121-130                      11 S
200-201                      11 S
202-204                      11 N
ID_RANGE                     CA RE
-------------------- ---------- --
205-207                       0 S
208-210                       0 N
211-212                      11 S
213-215                      11 N
300-301                      11 S
302-304                      11 N
305-307                       0 S
308-310                       0 N
311-312                      11 S
313-315                      11 N
 
已选择21行。
 
 
如果上面的方法需要表中的ID,CA,REG不能有重复的行,否则可能会得到不同的结果。
 
SQL> insert into t values (100, 11, 'S');
已创建 1 行。
SQL>
SQL> insert into t values (101, 11, 'S');
已创建 1 行。
SQL>
SQL> insert into t values (103, 11, 'S');
已创建 1 行。
SQL>
SQL> insert into t values (103, 11, 'S');
已创建 1 行。
SQL>
SQL> insert into t values (104, 11, 'N');
已创建 1 行。
SQL> SELECT  TO_CHAR(MIN(ID))||'-'||TO_CHAR(MAX(ID)) ID_RANGE,CA,REG
  2  FROM
  3  (
  4  SELECT CA,REG,ID,ID-ROW_NUMBER() OVER (PARTITION BY CA,REG ORDER BY ID) R
  5  FROM T
  6  )
  7  GROUP BY CA,REG,RN
  8  ORDER BY 1
  9  /
ID_RANGE                     CA RE
-------------------- ---------- --
100-100                      11 S
100-103                      11 S
101-103                      11 S
102-104                      11 N
104-104                      11 N
105-107                       0 S
108-110                       0 N
111-112                      11 S
113-115                      11 N
116-117                       0 S
118-120                       0 N
ID_RANGE                     CA RE
-------------------- ---------- --
121-130                      11 S
200-201                      11 S
202-204                      11 N
205-207                       0 S
208-210                       0 N
211-212                      11 S
213-215                      11 N
300-301                      11 S
302-304                      11 N
305-307                       0 S
308-310                       0 N
ID_RANGE                     CA RE
-------------------- ---------- --
311-312                      11 S
313-315                      11 N
已选择24行。
SQL> SELECT  TO_CHAR(MIN(ID))||'-'||TO_CHAR(MAX(ID)) ID_RANGE,CA,REG
  2  FROM
  3  (
  4  SELECT CA,REG,ID,ID-ROW_NUMBER() OVER (PARTITION BY CA,REG ORDER BY ID) R
  5  FROM (SELECT DISTINCT ID,CA,REG FROM T)
  6  )
  7  GROUP BY CA,REG,RN
  8  ORDER BY 1;
ID_RANGE                     CA RE
-------------------- ---------- --
100-101                      11 S
102-104                      11 N
103-103                      11 S
105-107                       0 S
108-110                       0 N
111-112                      11 S
113-115                      11 N
116-117                       0 S
118-120                       0 N
121-130                      11 S
200-201                      11 S
ID_RANGE                     CA RE
-------------------- ---------- --
202-204                      11 N
205-207                       0 S
208-210                       0 N
211-212                      11 S
213-215                      11 N
300-301                      11 S
302-304                      11 N
305-307                       0 S
308-310                       0 N
311-312                      11 S
313-315                      11 N
 
已选择22行。
SQL>
通过对DISTINCT去重复 就可以得到正确的结果。
 
阅读(1818) | 评论(0) | 转发(0) |
0

上一篇:PL/SQL Practices ---ORA-22160

下一篇:GDB教程

给主人留下些什么吧!~~