- 浏览: 425928 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
Glogo:
楼主您好,我在试验您的代码的时候发现新开的三个子线程并没有一直 ...
java 高并发 ReentrantLock -- 可重入的锁 -
univasity:
最近发觉也被限速了,投诉一下就好一会~~ 看来明天又要和电信M ...
ADSL上网速度慢 都是帐号限速惹的祸 -
liuyuanhui0301:
java 高并发 ReentrantLock -- 可重入的锁 -
dang_java:
呵.很好的说明文档.
JXTA技术与应用发展 -
helloqidi:
谢谢,学习了
SQL中exists和in的区别
1. 问题的提出
在实际工作中,我们常常会碰到号段选取的问题,例如:一组连续的数,去掉中间一些数,要求出剩下的数的区间(即号段)
例如:一串数字为1,2,3,4,7,9,10,则号段为1-4,7-7,9-10
知道号段的起止,要求出该号段内所有的数
例如:号段为1-3,15-15,则号段内所有的数为1,2,3,15
一组数,中间可能有断点,要求出缺失的数
例如:一串数字为1,2,3,4,7,9,10,则缺失的数为5,6,8
已知大号段范围及已用号段范围,求可用号段范围
例如:大号段范围0-999,已用号段范围0-200,399-599,则可用号段范围为201-398,600-999
2. 基础知识
先做下热身运动,回顾一下层次查询和lead/lag函数的运用。
2.1 伪列rownum和level
伪列就是并非在表中真正存在的列。已有很多资料介绍rownum和level这两个伪列。这里只想强调一点,伪列是只针对结果集的。
2.2 利用层次查询构造连续的数
产生5~8这4个连续的数
select * from (select rownum+4 from dual connect by rownum<5);
select * from (select level+4 from dual connect by level<5);
以8月为界,例如2005年8月1日,之前的在校学生入学年份为2001~2004,之后的为2002~2005.求当前日期下的在校学生入学年份:
select * from (select to_char(add_months(sysdate, 4), 'yyyy') - rownum from dual connect by rownum<5);
2.3 用分析函数Lead和Lag获得相邻行的字段值
select rn, lag(rn)over(order by rn) previos, lead(rn)over(order by rn) next from (select rownum+4 rn from dual connect by rownum<5);
RN PREVIOS NEXT
---------- ---------- ----------
5 6
6 5 7
7 6 8
8 7
简单的说,在这里,Lag是获得前一行的内容,而Lead是获得后一行的内容。
select rn, lag(rn,2,-1)over(order by rn) previos, lead(rn,2,-1) over(order by rn) next from (select rownum+4 rn from dual connect by rownum<5);
RN PREVIOS NEXT
---------- ---------- ----------
5 -1 7
6 -1 8
7 5 -1
8 6 -1
这里,通过指定offset参数来获得两行前的内容和两行后的内容,如果offset超出范围并且未设定默认值-1,那么系统会自动将其值设为NULL.
3. 问题的解决
有了基础知识的积累,我们就可以解决前面提到的问题。
3.1 已知号码求号段
3.1.1 题例
我有一个表结构,
fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125
(第二个字段内可能是连续的数据,可能存在断点。)
怎样能查询出来这样的结果,查询出连续的记录来。
就像下面的这样?
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125
3.1.2 解答
思路:利用lead取得下一行的kshm,然后和本行的kshm想比,如果差值为1,说明这一行和下一行是连续的。由于首尾的特殊性,故而需要先用max和min来获得首尾点。
select fphm, nvl(lag(e)over(partition by fphm order by s),minn) ST, nvl(S,maxn) EN from
(select fphm, lag(kshm,1) over(partition by fphm order by kshm) S, kshm E, min(kshm)over(partition by fphm) minn, max(kshm) over(partition by fphm) maxn from t)
where nvl(E-S-1,1)<>0;
FPHM ST EN
---------- ---------- ----------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009
3.2 根据号段求出包含的数
3.2.1 题例
有表及测试数据如下:
CREATE TABLE T20
(
ID NUMBER(2),
S NUMBER(5),
E NUMBER(5)
);
INSERT INTO T20 ( ID, S, E ) VALUES ( 1, 10, 11);
INSERT INTO T20 ( ID, S, E ) VALUES ( 2, 1, 5);
INSERT INTO T20 ( ID, S, E ) VALUES ( 3, 88, 92);
COMMIT;
S为号段起点,E为号段终点,求出起点和终点之间的数(包括起点和终点)
3.2.2 解答
很明显,这需要构造序列来解决问题
select a.id, a.s, a.e,b.dis, a.S+b.dis-1 h from
t20 a,
(select rownum dis from
(select max(e-s)+1 gap from t20)
connect by rownum<=gap) b
where a.e>=a.s+b.dis-1
order by a.id, 4
运行结果:
ID S E DIS H
---------- ---------- ---------- ---------- ----------
1 10 11 1 10
1 10 11 2 11
2 1 5 1 1
2 1 5 2 2
2 1 5 3 3
2 1 5 4 4
2 1 5 5 5
3 88 92 1 88
3 88 92 2 89
3 88 92 3 90
3 88 92 4 91
3 88 92 5 92
我们再看下面这种做法:
select a.id, a.s, a.e,rownum, a.S+rownum-1 h from
t20 a ,
(select id, e-s+1 gap from t20 where id=2) b
where a.id=b.id
connect by rownum<=gap
ID S E ROWNUM H
---------- ---------- ---------- ---------- ----------
2 1 5 1 1
2 1 5 2 2
2 1 5 3 3
2 1 5 4 4
2 1 5 5 5
嗯,得到的结果也是正确的,若我们把粗斜体字部分去掉后,看看结果是什么样:
ID S E ROWNUM H
---------- ---------- ---------- ---------- ----------
1 10 11 1 10
1 10 11 2 11
2 1 5 3 3
2 1 5 4 4
2 1 5 5 5
2 1 5 6 6
3 88 92 7 94
这样的结果,显然不是我们需要的,更何况,这是错误的。由此更能深入理解,伪列是只针对结果集的。
3.3 求缺失的号3.3.1 题例table T,列:serial_no我想能够查询一下serial_no这个字段的不连续的值。
例如:
serial_no
1
2
3
4
6
8
9
10
我想一个sql语句查出来缺失的号码
显示结果为:
5
7
3.3.2 解答思路:找出数B和它前面的数A进行比较(数按从大到小进行排序),如果B-A=1,则说明是连续的,中间没有断点。
select distinct s+level-1 rlt from (select lag(serial_no,1) over(order by serial_no)+1 S, serial_no-1 E from t) where E-S<>0 connect by level<=e-s
3.4 求尚未使用的号段
3.4.1 题例
表A结构:
bill_type_id varchar2(1),
bill_start number,
bill_end number,
office_level varchar2(4)
数据如下:
A 0 999 1
A 0 199 2
A 300 499 2
A 700 799 2
sql目的是取出包含在level1级别里的,还没有录入level2级别的号段。
3.4.2 解答这个好像是3.1和3.3这两个问题的逆问题创建表及测试数据:
CREATE TABLE T8
(
A NUMBER(4),
B NUMBER(4),
C NUMBER(4),
Q VARCHAR2(1 BYTE)
);
Insert into T8(A, B, C, Q)Values(555, 666, 2, 'A');
Insert into T8(A, B, C, Q)Values(100, 199, 2, 'A');
Insert into T8(A, B, C, Q)Values(0, 999, 1, 'A');
Insert into T8(A, B, C, Q)Values(300, 499, 2, 'A');
COMMIT;
思路:将大号段的边界与小号段的边界相比,从大号段中将小号段“挖”掉,这样剩下的就是可用号段了。
select S,E from
(
SELECT NVL2(LAG(A)OVER(PARTITION BY Q ORDER BY A), B+1, MIN(A)OVER(PARTITION BY Q)) S,
NVL(LEAD(A)OVER(PARTITION BY Q ORDER BY A)-1, MAX(B)OVER(PARTITION BY Q)) E
from t8 START WITH C=1 CONNECT BY C-1 = PRIOR C AND Q= PRIOR Q
)
where s<=e
运行结果:
S E
---------- ----------
0 99
200 299
500 554
667 999
参考资料
Oracle 8i SQL Reference
论坛相关讨论帖子
请教查询语句的写法? http://www.itpub.net/435578.html
如何sql查询出连续号码段 http://www.itpub.net/354052.html
请教关于号段选取的sql写法 http://www.itpub.net/480536.html
知道号段起止,如何选择该号段内的所有号码? http://www.itpub.net/701508.html
发表评论
-
mysql查询优化之索引类型、最左前缀
2012-03-22 00:42 1088一、什么是索引? 索引用来快速地寻找那些具有特定值的记录, ... -
Checkpoint not Complete
2009-04-30 15:36 1503alter_ORCL.log如下: Wed Jun 25 0 ... -
How to resize redo log file?
2009-04-30 15:33 1067I wrote a script named 'checkRe ... -
Oracle: REDO&UNDO
2009-04-25 13:41 1040在这里会介绍UNDO,REDO ... -
DBCP,数据库断开自动连接的问题及解决方法
2009-04-20 13:34 2720以前公司开发的一个项 ... -
Oracle : How to drop database
2009-04-06 01:05 1037字号: 大大 中中 小小 D:\>set or ... -
执行计划和执行顺序
2009-04-02 17:53 941如果要了解执行计划和执行顺序,必须理解执行计划的 ... -
Oracle timestamp
2009-02-01 10:47 3083select systimestamp from dual s ... -
Oracle日期函数与应用
2009-01-19 16:26 1166相信很多人都有过统计某些数据的经历,比如,要统计财务的情况 ... -
start with ... connect by用法简介
2008-12-22 17:17 1283sql有向图问题期待新解决方案 /************* ... -
START WITH and CONNECT BY in Oracle SQL
2008-12-22 16:55 1301A simple example In the follow ... -
利用伪列算出每个月那几天是星期天(ORACLE)
2008-12-22 16:05 15591.首先我们要用到LEVEL .... CONNE ... -
有关Oracle表分区进行(DML)维护后对索引的影响的分析
2008-12-15 14:30 2215今天对数字电视系统的 ... -
Oracle表分区和索引分区精彩汇总
2008-12-15 14:29 1985分区概述 为了简化数据库大表的管理,例如在数据仓库中 ... -
为什么有时Oracle数据库不用索引来查找数据?
2008-12-15 14:24 1256当你运用SQL语言,向数 ... -
怎样成长为一个真正的Oracle DBA
2008-12-15 12:59 933本文网址:http://bbs.bitscn.com/1674 ... -
MYSQL编码问题
2008-12-04 21:56 672一.原理篇Mysql的字符 ... -
ORACEL SQL 性能优化
2008-10-16 15:18 12451. 选用适合的ORACLE优化器 ORACLE的优化器共有 ... -
mysql 的mysql数据库
2008-09-20 17:06 1031授权表的用途: ... -
Hibernate注释简介
2008-09-19 13:54 1122在过去几年里,Hibernate不断发展,几乎成为Java数据 ...
相关推荐
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫淡、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
8. Oracle层次查询:层次查询是Oracle数据库特有的查询类型,用于处理层级数据,本书将提供层次查询的使用技巧和高级应用。 9. 递归WITH编程:递归WITH子句是Oracle 11gR2引入的新特性之一,用于简化递归查询,本书...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
此外还有大量案例:Where In List问题解析,数据库设计和大数据量处理、数据审核、号段选取应用、分析SQL执行计划的关注点、Oracle开发误区探索、提升PL/SQL开发性能漫谈、管道函数的学习与实战应用、巧用锁特性避免...
列举所有证件号码段对应的省份、城市、区镇。 通过insert into插入表格。 如 insert into card_info(provinceID,cityID,regionID,province,city,region) values(13,1305,130524,河北省,邢台市,柏乡县). 可...