首页 > 代码库 > 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、未完,待续。。。。