OracleSQL优化一(SQL使用技巧)

1、行列转换:

  decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值);

 

  select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; –取较小值

 

  sign()函数根据某值是0、正数还是负数,分别返回0、1、-1

  例如:

  变量1=10,变量2=20

  则sign(变量1-变量2)返回-1,decode解码结果也“变量1”,达到了取得较小值的目的。

 

举例:查询emp表中之每个部门的口?

SELECT sum(decode(deptno,10,1,0)) as 部门10,
            sum(decode(deptno,20,1,0)) as 部门20,
            sum(decode(deptno,30,1,0)) as 部门30 from emp

Oracle 1

      

      做个比:select deptno,count(deptno) from emp group by deptno

      Oracle 2

 

2、递归查询的优化

   lpad/rpad( string, padded_length, [ pad_string ] )

   说:字符不够的时向左或为右侧填充。

   例如:SQL> select lpad(‘abcde’,10,’x’) from dual;

      LPAD(‘ABCDE’,10,’X’)

      xxxxxabcde

 

  1. SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF   
  2. FROM T_TREE   
  3. START WITH FATHER_ID = 0   
  4. CONNECT BY PRIOR ID = FATHER_ID;  

 

解释:CONNECT_BY_ISLEAF
判断该行记录是否也叶子节点,如果是返回1,否则返回0

          START WITH FATHER_ID = 0 规定啊一样长达记下也清节点

          CONNECT BY PRIOR ID = FATHER_ID
判断是节点的父节点是啦一样漫长记下

 

例子:

 SELECT LPAD(MENU_NAME,

            LENGTHB(MENU_NAME) + LEVEL,

            DECODE(CONNECT_BY_ISLEAF, 1, ‘ | ‘, ‘+’))

  FROM MENU

 START WITH PID = ‘0’

 CONNECT BY PRIOR ID = PID;

 

3、利用分析函数排序和去重

http://blog.csdn.net/haiross/article/details/15336313#comments

 

分析函数是啊?
剖析函数是Oracle特意用于缓解复杂报表统计需求的功能强大的函数,它好以数遭到展开分组然后计算基于组的某种统计值,并且每一样组的各级一行还可以回去一个统计值。

          

剖析函数和聚合函数的不同之处是呀?
平常的聚合函数用group
by分组,每个分组返回一个统计值,返回的字段名只能是分开组名。而分析函数采用partition
by分组,并且每组每行都可以回到一个统计值,返回的字段名好是每个字段,因为是本着许交记录的,所以无涉及。

              

解析函数的花样
剖析函数带有一个开窗函数over(),包含三只分析子句:分组(partition by),
排序(order by), 窗口(rows) ,
他们的运形式如下:over(partition by xxx
order by yyy rows between zzz)

 

 开窗函数over()含蓄三单分析子句:分组子句(partition by),
排序子句(order by), 窗口子句(rows)
      窗口就是分析函数分析时只要拍卖的数据范围,就拿sum来说,它是sum窗口中之笔录而未是整分组中之笔录,因此我们在怀念获取有栏位的累计值时,我们要拿窗口指定到拖欠分组中之首先实施数据到即推行,
如果你指定该窗口起该分组中之第一尽到最后一实行,那么该组中之各级一个sum值都见面同样,即所有组的总数。

     

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):当前组第一行到当前行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING):当前行到最后一行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW):当前行的上一行(rownum-1)到当前行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING):当前行的上一行(rownum-1)到下两行(rownum+2)的汇总

 

假使不管是否省略分组子句,如下结论都是树立的:

 

 1、窗口子句不可知独出现,必须发order by子句时才会冒出。

 2、当省略窗口子句时:
    a) 如果是order by则默认的窗口是unbounded preceding and current
row  –当前组的率先实施至即实践,即以时下组中,第一履到目前实行,这里强调一下,如果partition
by字段和order by 字段一样的话,这个order by不奏效,相当给简单了order by

    b) 如果以看望略order by则默认的窗口是unbounded preceding and
unbounded following  –整个组

 

些微只order by的实践会

剖析函数(以及与其配合的开窗函数over())是以总体sql查询了后(sql语句被之order
by的施行于特殊)再拓展的操作, 也就是说sql语句被的order
by也会潜移默化分析函数的尽结果:

 

    a) 两者一致:如果sql语句被之order
by满足与分析函数配合的开窗函数over()分析时讲求的排序,即sql语句被的order
by子句里的情节及开窗函数over()中之order
by子句里的内容同样,那么sql语句被的排序将先实行,分析函数在分析时就不用还排序;
    b) 两者不一致:如果sql语句被的order
by不满足与析函数配合的开窗函数over()分析时讲求的排序,即sql语句被的order
by子句里的情节及开窗函数over()中的order
by子句里的情未一致,那么sql语句被的排序将最终当条分缕析函数分析了后实施排序。

 

 

常用之解析函数:

  1、row_number() over(partition by … order by …) 
为各国一样条记下返回一个唯一的价值。当遇到相同数量常常,排名按记录集中记录之逐条依次递增,现实状况也:个人于分组内的排名
  2、rank() over(partition by … order by …) 
得到各级条记下在数量被的排行,排名不腾
  3、dense_rank() over(partition by … order by …)
得到各级条记下在多少中之排名,排名跳跃
  4、count() over(partition by … order by …)
每个分组中,某个字段的统计
  5、max() over(partition by … order by …)
  6、min() over(partition by … order by …)
  7、sum() over(partition by … order by …)
  8、avg() over(partition by … order by …)
  9、first_value() over(partition by … order by …)
得到第一单记录值
  10、last_value() over(partition by … order by …)
得到最终一个记录值
  11、lag() over(partition by … order by …)
lag函数可以于平等次等查询中取出同一字段的前方n行的数据

  12、lead() over(partition by … order by …)
lead函数可以当同一次询问中取出同一字段的后n行的值

 

 

lag(arg1,arg2,arg3)
率先独参数是列名,
次个参数是偏移的offset,
其三独参数是超乎记录窗口时之默认值。

select id,name,lag(name,1,0) over(order by id) from kkk; 

 

 例子:select ename,deptno,sal,sum(sal) over(partition by deptno order
by ename) as A from emp

Oracle 3

 

去重:

  1、利用rowid的唯一性查询或删除重复数据

  
select ROWNUM,ROWID,d1.* from dept2 d1 where d1.rowid=(select min(d2.rowid) from dept2 d2 where d2.deptno=d1.deptno);

  2、给定重复行序号并去再

SELECT FWZL

    FROM (SELECT FWZL,

                 ROW_NUMBER() OVER(PARTITION BY FWZL ORDER BY ID DESC)
RN

            FROM T_FWXX) F

   WHERE F.RN = 1

 

 4、求占比较、小计和一起

      分析函数RATIO_TO_REPORT
用来计算时记下之指标expr占初始窗函数over中涵盖记录的具有同一指标的百分比.
这里要开窗函数的统计结果吗null或者为0,就是说占用率的为除数为0或者为null,
则获的结果吧也0. 开窗条件query_partition_clause决定让除数的值,
如果用户忽略了之条件, 则计算查询结果负装有记录的集中值.
用户不能够运用外分析函数或者ratio_to_report作为分析函数ratio_to_report的参数expr,
也就是说这个函数

 百分割比(求者字段值占整组的百分比):select deptno,ename,empno,round(RATIO_TO_REPORT(sal) OVER(PARTITION BY deptno)*100,1) 百分比 from emp

Oracle 4

 

rollup()与cube():排列组合分组 

1)、group by rollup(a, b, c):
率先会针对(a、b、c)进行group by,
然后重新针对(a、b)进行group by,
尔后更指向(a)进行group by,
末段对全表进行集中操作。

 

2)、group by cube(a, b, c):
尽管如此第一会针对(a、b、c)进行group by,
然后依次是(a、b),(a、c),(a),(b、c),(b),(c),
末尾对全表进行汇总操作。

 

 

ROLLUP,是GROUP
BY子句的平等种扩大,可以为每个分组返回小计记录和为保有分组返回总计记录。

CUBE,也是GROUP
BY子句的一样种扩大,可以回去各国一个列成的小计记录,同时在末加上总计记录。

 

呼吁每个分组的小计和合:select deptno,sal,sum(sal) from emp group by
rollup(deptno,sal)

Oracle 5

 

要诸一个列的三结合的小计记录:select deptno,sum(sal) from emp group by
cube(deptno,sal)

Oracle 6

 

聚合函数(within group语法):

 

select rank(1500) within group (order by sal desc) “rank of 1500” from
emp;

 

   
解释:如果存在一样长条记下,这长达记下的salary字段值为1500。这就是说将拖欠修记下插入emp表中后,按照sal字段降序排列后,该条记下之序号为多少?

   得应用within
group关键字的函数有rank,dense_rank,PERCENT_RANK,PERCENTILE_CONT,PERCENTILE_DISC等

*   Oracle 7*

 

 

 5、单条记下插入多表

      原理:利用一个insert all 语法:insert all when .. then

     

INSERT ALL WHEN LOCALE = 1 THEN INTO EMPLOYEE1(ID, LOCALE, NAME, AGE,
GENDER, CODE) VALUES (ID, LOCALE, NAME, AGE, GENDER, CODE)

                  WHEN LOCALE = 32 THEN INTO EMPLOYEE2(ID, LOCALE, NAME,
AGE, GENDER, CODE) VALUES(ID, LOCALE, NAME, AGE, GENDER, CODE)

 

 

insert all into table values()

              into  table values()

 

 

 6、Merge的使用

 

 解释:DML语句,适用于批量拍卖

   MERGE INTO table_name alias1 
  USING (table|view|sub_query) alias2 
  ON (join condition) 
  WHEN MATCHED THEN 
  UPDATE table_name
  SET col1 = col_val1, 
  col2 = col2_val where 条件
  WHEN NOT MATCHED THEN 
  INSERT (column_list) VALUES (column_values) where 条件;

 

MERGE INTO EMPLOYEE E

USING (SELECT * FROM EMPLOYEE1) E1

ON (E.NAME = E1.NAME)

WHEN MATCHED THEN

    UPDATE SET E.CODE = E1.CODE, E.AGE = E1.AGE

WHEN NOT MATCHED THEN

   INSERT  (E.ID, E.LOCALE, E.NAME, E.AGE, E.GENDER, E.CODE)
VALUES(E1.ID, E1.LOCALE, E1.NAME, E1.AGE, E1.GENDER, E1.CODE);

 

备考:对少数摆设表的简单单字段相配合,如果配合上了即开创新操作,否则就算举行插入操作。

 

 7、KEEP的使用

    
keep是Oracle下的其他一个解析函数,他的用法不同为经过over关键字指定的解析函数,可以用于这样同样种植场合下:取同一个分组下坐有字段排序后,对点名字段取最小或者太特别之怪值。

   

  一般写法是 MIN [ MAX ] (A) KEEP(DENSE_RANK FIRST [ LAST ] ORDER
BY B),这里引用他人说的明的解释一下:

DENSE_RANK

效能描述:根据ORDER
BY子句被表达式的价值,从询问返回的各国一样实行,计算其与任何行的相对位置。组内的数据按ORDER
BY子句排序,然后让各级一样行赋一个哀号,从而形成一个队列,该队从1起,往后长。每次ORDER
BY表达式的价值发生变化时,该队也跟着增多。有同一值的行得到同样的数字序号(认为null时等的)。密集的队列返回的常未尝间隔的一再。

 

FIRST

功效描述:从DENSE_RANK返回的聚众中取出排在最为前的一个价值的实践(可能多行,因为价值可能相当于),因此总体的语法需要在开始处于加上一个集合函数以从中取出记录。

LAST

力量描述:从DENSE_RANK返回的汇中取出排在结尾当之一个值的尽(可能大多尽,因为价值可能当),因此整体的语法需要以开头处于加上一个集合函数以从中取出记录。

从而默认排序下,FIRST可以知道是取小值,LAST取大值。而前的MIN或者MAX则是于KEEP的结果集中取某一字段的极端特别价值或极小价。

 

 
keep和平常分析函数的界别:普通的分析函数只是列出分组后底记录,而针对性各国一个组的笔录进行统计分析。

                            keep对分组内之函数通过order
by和max(),min()选取某字段的值。可以了解成这里的keep就是sum() groud by
deptno 前的sum()那样

 

  实例:取出各个部门薪资最高的职工编号

 
1、select deptno,empno,sal,max(empno) keep(dense_rank first order by sal desc) over(partition by deptno) from emp2

  2、select deptno,max(empno) keep(dense_rank first order by sal desc) from emp2 group by deptno 

        解释:按deptno分组,再对分组中的sal降序,取出第一只sal的员工号

Oracle 8

 

 

8、SQL查询正则表达式的运

 

  ORACLE中的支持正则表达式的函数主要出脚四个:
1,REGEXP_LIKE :与LIKE的效能相似

    select * from emp where regexp_like(empno,’7[0-9]{2}9′)
2,REGEXP_INSTR :与INSTR的功力相似

 

REGEXP_INSTR

6个参数

先是个是输入的字符串

仲单是正则表达式

其三个凡是标识由第几只字符开始正则表达式匹配。(默认为1)

季只凡是标识第几独匹配组。(默认为1)

第五只凡是依定返回值的品类,如果该参数为0,则归回值为配合配位置的率先独字符,如果该值为非0则归匹配配值的末尾一个岗位。

第六只是是取值范围:

i:大小写不灵动;

c:大小写敏感;

n:点号 . 不配合配换行符号;

m:多实践模式;

x:扩展模式,忽略正则表达式中之空白字符。

    SELECT REGEXP_INSTR(a,'[0-9]+’) AS A FROM test_reg_substr;

 

3,REGEXP_SUBSTR :与SUBSTR的功效相似

   

REGEXP_SUBSTR函数格式如下:

   function REGEXP_SUBSTR(String, pattern, position, occurrence,
modifier)
__srcstr     :需要展开正则处理的字符串
__pattern    :进行匹配的正则表达式
__position   :起始位置,从第几只字符开始正则表达式匹配(默认为1)
__occurrence :标识第几单匹配组,默认为1
__modifier  
:模式(’i’不分轻重缓急写进行搜寻;’c’区分轻重缓急写进行查找。默认为’c’。)

  1. –1、查询利用正则分后的首先只价,也尽管是34  
  2. SELECT REGEXP_SUBSTR(‘34,56,-23′,'[^,]+’,1,1,’i’) AS STR FROM DUAL;  
  3. –结果是:34   
  4. –2、查询利用正则分后底末梢一个价,也就算是-23  
  5. SELECT REGEXP_SUBSTR(‘34,56,-23′,'[^,]+’,1,3,’i’) AS STR FROM DUAL;  
  6. –结果是:-23  

4,REGEXP_REPLACE :与REPLACE的效益相似

 

 

 

 

    

9、常见函数

TRUNC:截取函数

EXTRACT:用于从一个date或者interval类型中截取到特定的有些

NVL

DECODE

length:字符长度

lengthb:字节长度

ASCII

INITCAP:首字母大写

 

SOUNDEX:返回由四单字符组成的代码 (SOUNDEX) 以评估两独字符串的相似性

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND:函数用于把数值字段舍入为指定的微数位数

 

10、分页函数

 

–普通写法

 

SELECT AA.FWZL, AA.FWTYBH

  FROM (SELECT A.FWZL, A.FWTYBH, ROWNUM RN

          FROM (SELECT F.FWZL, F.FWTYBH FROM FW F ORDER BY F.FWTYBH
DESC) A

         WHERE ROWNUM <= 120020) AA

 WHERE AA.RN > 120000;

 

–rowid写法

SELECT /*+ ROWID(FW) */ FW.FWZL, FW.FWTYBH

   FROM FW FW,

        (SELECT AA.RID, AA.RN

           FROM (SELECT A.RID, ROWNUM RN

                  FROM (SELECT /*+ index(F IDX_FW_FWTYBH) */

                         ROWID RID

                          FROM FW F

                          ORDER BY F.FWTYBH DESC) A

                 WHERE ROWNUM <= 120020) AA

          WHERE AA.RN > 120000) B

  WHERE FW.ROWID = B.RID;

相关文章