首页 > 代码库 > ORACLE-SQL微妙之处

ORACLE-SQL微妙之处

本文总结一下平时经常使用的SQL语句以及一些ORACLE函数的微妙之处。欢迎大家多多补充平时最常用的SQL语句,供大家学习参考。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SQL> select * from temp2;
NAME            SORCE
---------- ----------
1                  43
2                  23
3                  42
4                  87
5                  12
<span style="font-size: 14px;"><strong><span style="color: rgb(255, 0, 0);">1、数据累加</span></strong></span>
SQL> SELECT NAME, sum(sorce) OVER(ORDER BY NAME)
  2    FROM temp2
  3   ORDER BY NAME;
NAME       SUM(SORCE)OVER(ORDERBYNAME)
---------- ---------------------------
1                                   43
2                                   66
3                                  108
4                                  195
5                                  207
<span style="font-size: 14px;"><strong><span style="color: rgb(255, 0, 0);">2、去掉最大值和最小值</span></strong></span>
SQL> SELECT NAME,
  2         sorce,
  3         LAG(sorce) over(order by sorce)  Lag_List,
  4         LEAD(sorce) over(order by sorce)  Lead_List
  5    FROM temp2;
NAME            SORCE        Lag       Lead
---------- ---------- ---------- ----------
5                  12                    23
2                  23         12         42
3                  42         23         43
1                  43         42         87
4                  87         43

 3、著名分析函数--排序

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> SELECT name,
  2         value,
  3         RANK() OVER(order by value) RANK_SORT,
  4         DENSE_RANK() OVER(order by value) DENSE_SORT,
  5         ROW_NUMBER() OVER(order by value) ROW_SORT
  6    FROM sorce;
NAME       VALUE       RANK_SORT DENSE_SORT ROW_SORT
---------- ------ ---------- ---------- ----------
wu          21          1          1          1
zhang       60          2          2          2
Li          70          3          3          3
xue         119         5          5          5
<span style="color: rgb(255, 0, 0);">wang        130         6          6          6
chen        130         6          6          7
sun         175         8          7          8</span>
zhao        285         9          8          9
su          359         10         9          10
Li          480         11         10         11<br>可见三者的区别:<br>RANK()OVER():如果值相同,则两者顺序号相同,随机一个在另外一个的上边,而且顺序号会有间断,不是连续的;<br>DENSE_RANK():如果值相同,则两者顺序号相同,随机一个在另外一个的上边,而且顺序号仍然是连续的,不存在断层的现象;<br>ROW_NUMBER():如果值相同,则两种顺序号不同,安装顺序号依次排开,而且顺序号是连续的。

 4、TRANSLATE()函数

translate函数与replace类似,但是又与replace不同,translate指定字符串string中出现的from_str,将from_str中各个字符替换成to_str中位置顺序与其相同的to_str中的字符。

SQL Reference中给的例子:SELECT TRANSLATE(‘SQL*Plus User‘‘s Guide‘, ‘ */‘‘‘, ‘___‘) FROM DUAL;

巧用:

(1)判断一个字符串是数字

    SELECT TRANSLATE(‘ABC123‘,‘#1234567890.‘,‘#‘) FROM DUAL;

(2)统计字符E出现的次数

    SELECT LENGTHB(TRANSLATE(‘ABCDEFGEFGDBE‘,‘E‘||‘ABCDEFGEFGDBE‘,‘E‘)) FROM DUAL;

5、ROUND()函数

我们平时用得最多的是第一种,用ROUND()函数作为数据四舍五入运算,其实ROUND函数还有第二种形式,对日期进行格式化操作,与TRUNC()函数类似。

如:SELECT ROUND(SYSDATE,‘yyyy‘) FROM DUAL;
  SELECT ROUND(SYSDATE,‘MM‘) FROM DUAL;
  SELECT ROUND(SYSDATE,‘HH24‘) FROM DUAL;

6、NVL相关函数

 NVL相关的函数有:NVL(expr1,expr2),NVL2(expr1,expr2,expr3),NULLIF(expr1,expr2),DECODE(expr1,expr2,value1,expr3,value2...,default)

  (1) NVL(expr1,expr2) :如果expr1为空,则用expr2来替换。

  (2) NVL2(expr1,expr2,expr3) :如果expr1非空,则返回expr2,否则返回expr3。

  (3) NULLIF(expr1,expr2):将expr1和expr2做比较,如果想等,则返回null,否则返回expr1。

  (3) DECODE(expr1,expr2,value1,expr3,value2...,default):如果expr1与expr2相等,则返回value1,如果expr1与expr3相等,则返回value2,...否则,返回default。

7、收集表的统计信息

收集表的统计信息方法有很多种: (1) ANALYZE  (2) DBMS_STATS

看起来很繁琐,其实用起来挺简单的。

如对某张表进行分析:

      ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;

目前,ORACLE官方推荐第二种方法。在DBMS_STATS包里有很多过程和方法,对SCHEMA、TABLE和INDEX进行收集统计信息相关的操作。

  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>‘SCOTT‘,TABNAME=>‘EMP‘,DEGREE=>4,CASCADE=>‘TRUE‘);

8、未完,待续。。。。