Oracle SQL常用内置函数总结

最新推荐文章于 2021-04-13 17:01:49 发布
最新推荐文章于 2021-04-13 17:01:49 发布
阅读量1.6w 收藏 66
点赞数 8
分类专栏： Oracle SQL & PL/SQL 文章标签： 数据库 sql SQL函数 oracle TIPTOP ERP
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yihuiworld/article/details/44701561
版权

Oracle SQL内置函数

Oracle内置SQL函数主要分为一下类别：数字函数、字符函数、日期函数、转换函数、集合函数、分析/聚合函数

本手册所有示例均以TIPTOP GP 5.10 ERP数据库表为基础演示。数据字典下载地址：http://pan.baidu.com/s/1jG1PGF0

 

1、数字函数

      abs(n)：返回数字n的绝对值

      ceil(n)：返回>=数字n的最小整数

      floor(n)：返回<=数字n的最大整数

      round(n,[m])：四舍五入运算,如果m缺省则四舍五入到整数位

                               m<0,四舍五入到小数点的前m位,m>0四舍五入到小数点的后m位

      trunc(n,[m])：截取数字,如果m缺省则将小数位截去

                              m<0,截取到小数点的前m位,m>0截取到小数点的后m位

--demo：abs()、ceil()、floor()、trunc()
SELECT abs(-2.3),
       ceil(-2.6),ceil(2.6),
       floor(-2.6),floor(2.6),
       round(-666.588),round(-666.588,2),round(-666.588,-2),
       trunc(-666.588),trunc(-666.588,2),trunc(-666.588,-2)
   FROM dual
--结果：

ABS(-2.3)                  2.3
CEIL(-2.6)                 -2
CEIL(2.6)                  3
FLOOR(-2.6)             -3
FLOOR(2.6)               2
ROUND(-666.588)      -667
ROUND(-666.588,2)   -666.59
ROUND(-666.588,-2)  -700
TRUNC(-666.588)       -666
TRUNC(-666.588,2)    -666.58
TRUNC(-666.588,-2)   -600

 

       sqrt(n)：返回数字n(n必须大于0)的平方根

       power(n,m)：返回数字n的m次幂，底数n和指数m都可以是任意数字,但是如果底数n为负数则指数m必须为正数

       exp(n)：返回e(2.71828183....)的n次幂

       ln(n)：返回数字n(n必须大于0)的自然对数

       log(n,m)：返回底数为n(除0及1的正整数)的m(任何正整数)对数

       mod(n,m)：返回数字n/m的余数,如果数字m=0则返回n

--demo：sqrt(),power(),exp(),ln(),log(),mod()
SELECT sqrt(4),power(2,3),exp(1),ln(10),log(2,8),mod(5,2)
  FROM dual
--结果：

SQRT(4)          2
POWER(2,3)     8
EXP(1)             2.71828182845905
LN(10)             2.30258509299405
LOG(2,8)          3
MOD(5,2)         1

 

       cos(n)：返回数字n(弧度单位表示的角度值)的余弦值

       cosh(n)：返回数字n的双曲余弦值

       acos(n)：返回数字n的反余弦值，求的结果单位为弧度，n的范围为 -1 < n < 1

       sin(n)：返回数字n(弧度单位表示的角度值)的正弦值

       sinh(n,m)：返回数字n的双曲正弦值

       asin(n,m)：返回数字n的反正弦值，求的结果单位为弧度，n的范围为 -1 < n < 1

       tan(n)：返回数字n(弧度表示的角度值)的正切值

       tanh(n)：返回数字n的双曲正切值

       atan(n)：返回数字n的反正切值，求的结果单位为弧度，n的范围任意数值

       atan(n,m)：返回数字n/m的反正切值，求的结果单位为弧度，你可以为任意数值，m不可为0

--三角函数应用demo：
SELECT cos(0.5),cosh(2),acos(1),
       sin(0.5),sinh(2),asin(0),
       tan(0.5),tanh(2),atan(8),atan(16/2)
 FROM dual

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


2、字符函数

      ascii(n)：返回字符c的ascii值

      chr(n)：将ascii值转换为对应的字符

      initcap(s)：将字符串s所有的单词(依空格或非字母字符划分单词区间)的首字母大写,其余小写

      lower(s)：将字符串s中所有的字符转换为小写

      upper(s)：将字符串s中所有的字符转换为大写

      concat(s1,s2)：将字符串s2连接在s1后面，等同于操作符||

      length(s)：将返回字串s的长度，返回的长度包括其中的所有空格(尾部空格也算)；如果s为null，则返回null

--字符函数demo：
SELECT ascii('A'),chr(65),concat('Hello','World'),
       initcap('hello woRld'),
       lower('HeLLo woRld'),upper('HeLLo woRld'),
       length('HeLLo woRld'),length('李艺辉')
  FROM dual

--结果：

ASCII('A')                         65
CHR(65)                            A
CONCAT('HELLO','WORLD')  HelloWorld
INITCAP('HELLOWORLD')     Hello World
LOWER('HELLOWORLD')      hello world
UPPER('HELLOWORLD')       HELLO WORLD
LENGTH('HELLOWORLD')     11
LENGTH('李YI辉')                 4

 

       lpad(s1,n,s2)：在字串s1的左端填充字串s2，直至填充后的s1的总长度为n

                                如果不指定s2则默认为空格

                                如果s1的长度>n，则直接返回s1左端的n个字符

       rpad(s1,n,s2)：在字串s1的右端填充字串s2，直至填充后的s1的总长度为n

                                如果不指定s2则默认为空格

                                如果s1的长度>n，则直接返回s1左端的n个字符

--lpad、rpad
SELECT lpad('a',10,'8'),lpad('LaaaaaaR',6,'8'),
       rpad('a',10,'8'),rpad('LaaaaaaR',6,'8')
  FROM dual

--结果：

lpad('a',10,'8')              888888888a

lpad('laaaaaar',6,'8')     laaaaa
rpad('a',10,'8')              a888888888
rpad('laaaaaar',6,'8')     laaaaa

   

       instr(s1,s2,n,m)：取得子串s2在字串s1中的位置

                                    n表示在s1中开始搜索的位置，m表示字串s2出现的次数

                                    如果n为负数，则表示从尾部开始搜索，n与m默认为1

SELECT instr('888terryterry','terry',1,1) ,instr('888terryterry','terry',1,2),
       instr('888terryterry','terry',-1,1),instr('888terryterry','terry',-1,2)
  FROM dual

--结果：

instr('888terryterry','terry',1,1)     4 

instr('888terryterry','terry',-1,1)    9
instr('888terryterry','terry',1,2)     9 
instr('888terryterry','terry',-1,2)    4

 

         substr(s1,m,n)：取得字串s1从m开始，长度为n的子串，m>0表示从头开始搜索，m<0表示从尾开始

SELECT substr('李藝輝',1,2),substr('terry',-4,3)
  FROM dual

--结果：

substr('李藝輝',1,2)       李藝
substr('terry',-4,3)       err

 

         ltrim(s1,set)：从左端开始逐一取得字串s1左端包含的set中的任何字符

                                当遇到不是set中的字符是，则结束并返回剩余结果

         rtrim(s1,set)：从右端开始逐一取得字串s1右端包含的set中的任何字符

                                当遇到不是set中的字符是，则结束并返回剩余结果

         trim(c FROM s2)：从字串s2的头部、尾部、或两端截去字符c（c只能够是一个字符）

                                      当遇到不是set中的字符是，则结束并返回剩余结果

SELECT ltrim('terry','t'),ltrim('terry','ter'),ltrim('terry','e'),
       rtrim('terry','y'),rtrim('terry','ry'),rtrim('terry','yre'),
       trim('e' from 'terry'),trim('t' from 'terry'),trim('y' from 'terryy')
  FROM dual

--结果：

ltrim('terry','t')        erry
ltrim('terry','ter')     y
ltrim('terry','e')       terry
rtrim('terry','y')       terr
rtrim('terry','ry')      te
rtrim('terry','yre')    t
trim('e'from'terry')  terry
trim('t'from'terry')   erry
trim('y'from'terryy') terr

 

         replace(s1,s2,s3)：将s1字串中的子串s2用s3替代，如果s2为null则返回原来的字串s1

                                        注意：如果s3为null，则会去掉子串s2

SELECT replace('李藝輝-TERRY','李藝輝','諸葛錢好'),
       replace('李藝輝-TERRY','李藝輝')
  FROM dual

--结果：

replace('李藝輝-TERRY','李藝輝','諸葛錢好')       諸葛錢好-TERRY
replace('李藝輝-TERRY','李藝輝')                     -TERRY

 

          translate(s1,froms,tos)：将字符串s1按照froms和tos的对应关系进行转换

SELECT translate('terry','abcdefgxyz','8888888666')
  FROM dual

--结果：

translate('terry','abcdefgxyz','8888888666')      t8rr6

 

            regexp_substr(s1,pattern,position,occurrence,match_parameter)：

                      按照正则表达式pattern从s1字串中的position位置开始

                      截取第occurrence次出现的匹配pattern的字串，matche_parameter为默认匹配的文本

                      position，occurrence，matche_parameter 默认为1,1," "

            regexp_replace(s1,pattern,position,occurrence,match_parameter)：

                      正则表达式扩展replace的功能,用于按照特定的表达式pattern的规则替换字串串s1

                      s1指定替换字符串，position指定起始搜索位置

                      occurrence指定替换出现的第n个字符串

                      matche_parameter指定默认匹配操作的文本串

            regexp_like()：用正则表达式扩展后的like

            regexp_instr()：用正则表达式扩展后的instr

SELECT regexp_substr('我的郵箱yihuiworld@126.com','[[:lower:]]{1,}+@{1}+[[:alnum:]]{1,}+\.{1}+[[:alpha:]]{1,}'),
       regexp_replace('這個屌http://www.space.com.tw/product','http://([[:alnum:]]+\.?){3,4}+[[:print:]]{1,}','www.terry.com')
  FROM dual

regexp_substr('我的郵箱yihuiworld@126.com','[[:lower:]]{1,}+@{1}+[[:alnum:]]{1,}+\.{1}+[[:alpha:]]{1,}')

--结果:   yihuiworld@126.com

regexp_replace('這個屌http://www.space.com.tw/product','http://([[:alnum:]]+\.?){3,4}+[[:print:]]{1,}','www.terry.com')

--结果：這個屌www.terry.com

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

3、日期函数

      sysdate：返回系统当前日期时间

      systimestamp：返回系统当前日期时间和时区

      current_date：返回当前回话时区所对应的日期和时间

      current_timestamp：返回当前回话时区所对应的日期时间

      localtimestamp：返回当前回话时区所对应的日期时间

      systimestamp：返回系统当前日期时间和时区

      sessiontimezone：返回当前回话所在的时区

      dbtimezone：返回资料库所在的时区

SELECT sysdate,systimestamp,current_date,current_timestamp,
       localtimestamp,sessiontimezone,dbtimezone
  FROM dual

--结果：

SYSDATE                         2015/3/25 13:29:38
SYSTIMESTAMP                25-3月 -15 01.29.38.867634 下午 +08:00
CURRENT_DATE               2015/3/25 13:29:38
CURRENT_TIMESTAMP      25-3月 -15 01.29.38.867641 下午 +08:00
LOCALTIMESTAMP            25-3月 -15 01.29.38.867641 下午
SESSIONTIMEZONE          +08:00
DBTIMEZONE                   +08:00

 

      add_months(d,n)：返回指定日期d之后(或前)的n個月所對應的日期時間;n>0：之后，n<0：之前

      extract()：用於從特定的日期時間值里取出所需要的特定數據(如日期、月份、日、時間等)

      last_day(d)：用於返回指定日期所在月份的最後一天

      next_day(d,char)：返回指定日期后的一個工作日(由char指定)所對應的日前

      round(d[,fmt])：返回日期時間的四舍五如結果，如果fmt指定年度則7月1日為分界線
                              如果fmt指定月則16日為分界線，如果fmt指定天則中午12:00為分界線

      trunc(d[,fmt])：用於截斷日期時間數據，如果fmt指定年度則結果為本年的1月1日
                             如果fmt指定月則結果為本月1日，如果fmt空則結果為截取日期時間數據中的日期

SELECT sysdate,add_months(sysdate,4),add_months(sysdate,-4),
       extract(MONTH from sysdate),last_day(sysdate),next_day(sysdate,'星期一'),
       round(sysdate,'YEAR'),round(sysdate,'MONTH'),round(sysdate,'DAY'),round(sysdate),
       trunc(sysdate,'YEAR'),trunc(sysdate,'MONTH'),trunc(sysdate,'DAY'),trunc(sysdate)
  FROM dual

--结果：

SYSDATE                                           2015/3/25 13:40:14
ADD_MONTHS(SYSDATE,4)                 2015/7/25 13:40:14
ADD_MONTHS(SYSDATE,-4)                2014/11/25 13:40:14
EXTRACT(MONTHFROMSYSDATE)         3
LAST_DAY(SYSDATE)                          2015/3/31 13:40:14
NEXT_DAY(SYSDATE,'星期一')              2015/3/30 13:40:14
ROUND(SYSDATE,'YEAR')                    2015/1/1
ROUND(SYSDATE,'MONTH')                 2015/4/1
ROUND(SYSDATE,'DAY')                      2015/3/29
ROUND(SYSDATE)                              2015/3/26
TRUNC(SYSDATE,'YEAR')                     2015/1/1
TRUNC(SYSDATE,'MONTH')                  2015/3/1
TRUNC(SYSDATE,'DAY')                      2015/3/22
TRUNC(SYSDATE)                              2015/3/25      

 

       to_timestamp(chr[fmt[,nls_param]])：將符合特定日期和時間按格式的字符串轉變為TIMESTAMP類型 

--查詢 2015-03-25 11:30:00 時間點時 oay_file 記錄 [时间搓不能>当前时间点 且 不能太早]
select * from oay_file as of timestamp to_timestamp('2015-03-25 11:30:00','YYYY-MM-DD HH24:MI:SS')

       month_between(d1,d2)：返回日期d1與d2之間相差的月份數

                                                如果d1<d2則返回負數，如果d1和d2的天數相同或都是月底則返回整數,否則oracle以每月31天為準來計算結果的小數部份   

select sysdate,months_between(sysdate,to_date('2012/06/30','YYYY/MM/DD'))
  from dual

--结果：

SYSDATE                                                     2015/3/25 13:49:40
MONTHS_BETWEEN(SYSDATE,TO_DATE          32.8572954002389      


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


4、数据库系统相关配置

       sys_context(context,attribute)：返回應用上下文特定屬性，其中context指定應用上下文名，attribute指定對應的屬性

select SYS_CONTEXT('USERENV','TERMINAL') terminal,
   SYS_CONTEXT('USERENV','LANGUAGE') language,
   SYS_CONTEXT('USERENV','SESSIONID') sessionid,
   SYS_CONTEXT('USERENV','INSTANCE') instance,
   SYS_CONTEXT('USERENV','ENTRYID') entryid,
   SYS_CONTEXT('USERENV','ISDBA') isdba,
   SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
   SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
   SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
   SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
   SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
   SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
   SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
   SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
   SYS_CONTEXT('USERENV','SESSION_USER') session_user,
   SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
   SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
   SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
   SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
   SYS_CONTEXT('USERENV','DB_NAME') db_name,
   SYS_CONTEXT('USERENV','HOST') host,
   SYS_CONTEXT('USERENV','OS_USER') os_user,
   SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
   SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
   SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
   SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
   SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
   SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
   SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
 from dual

--结果：

TERMINAL                           PCS13098
LANGUAGE                          SIMPLIFIED CHINESE_CHINA.AL32UTF8
SESSIONID                         26273213
INSTANCE                           1
ENTRYID 
ISDBA                                 FALSE
NLS_TERRITORY                  CHINA
NLS_CURRENCY                   RMB
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT             DD-MON-RR
NLS_DATE_LANGUAGE         SIMPLIFIED CHINESE
NLS_SORT                          BINARY
CURRENT_USER                  SZPF
CURRENT_USERID               55
SESSION_USER                   SZPF
SESSION_USERID                55
PROXY_USER 
PROXY_USERID 
DB_DOMAIN 
DB_NAME                            topprod
HOST                                  DOMAIN\PCS13098
OS_USER                             terrylee
EXTERNAL_NAME 
IP_ADDRESS                       192.168.5.227
NETWORK_PROTOCOL           tcp
BG_JOB_ID 
FG_JOB_ID                           0
AUTHENTICATION_TYPE        DATABASE
AUTHENTICATION_DATA                                                

 

       UID：返回當前會話用戶的ID號
       USER：返回當前會話所對應資料庫用戶名
       USERENV(parameter)：返回當前會話上下文的屬性信息
                                             其中parameter可取值:ISDBA、LANGUAGE、TERMINAL、CLIENT_INFO

select uid,user,userenv('ISDBA') from dual

--结果：

UID                             55
USER                           SZPF
USERENV('ISDBA')        FALSE

 

        sys_guid()：用於生成類型為RAW的16字節的唯一標識符,每次調用該函數都會生成不同的RAW數據

select sys_guid(),sys_guid() from dual

--结果：

SYS_GUID()                 1217A841ED2B6437E0530900A8C0B6F8
SYS_GUID()                 1217A841ED2C6437E0530900A8C0B6F8    

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


 

5、复杂单行函数

       coalesce(expr1[,expr2]...)：返回運算式中第一個not null運算式的結果

       decode(expr,search1,result[,search2,result2,..][,default])：返回匹配于特定運算式的結果

              如果search1匹配expr則返回result1，如果如果search2匹配expr則返回result2
              依次類推，如果沒有任何匹配關係則返回default

select coalesce('','','terry','zhanna'),
       decode('M','W','girl','M','boy','person')
  
  from dual

--结果：

coalesce('','','terry','zhanna')                       terry
decode('M','W','girl','M','boy','person')           boy             

 

        greatest(expr1[,expr2]...)：返回列表運算式expr1,expr2,...中值最大的一個
                                                      在比較之前，expr2等項會被隱含地轉換為expr1的時候據類型

        least(expr1[,expr2]...)：返回列表運算式expr1,expr2,...中值最小的一個
                                                在比較之前，expr2等項會被隱含地轉換為expr1的時候據類型

        nullif(expr1,expr2)：比較expr1和expr2，如果二者相等則返回null，否則返回expr1

select greatest('terry','888','zhanna','666'),
       least('terry','888','zhanna','666'),
       nullif('terry','zhanna'),nullif('1','1')
  from dual

--结果：

greatest('terry','888','zhanna','666')zhannanullif('terry','zhanna')terry
least('terry','888','zhanna','666')666nullif('1','1')

 

        sys_connect_by_path(column,char)：用於返回從根到子節點的列值路進，值適用于層次查詢

--展成品料號TE902C BOM下階元件
select lpad(' ',2*level-1)||sys_connect_by_path(bmb03,'|--') "TE902C展BOM元件" 
  from bmb_file
  start with bmb01 = 'TE902C'
  connect by prior bmb03 = bmb01

--结果：

 |--1300-00677002
   |--1300-00677002|--3230-00677000
   |--1300-00677002|--5410-TE902C02
   |--1300-00677002|--LPLA10002
     |--1300-00677002|--LPLA10002|--1800-00100117
       |--1300-00677002|--LPLA10002|--1800-00100117|--2101-01002000
       |--1300-00677002|--LPLA10002|--1800-00100117|--2101-02001034
     |--1300-00677002|--LPLA10002|--1800-00100118
       |--1300-00677002|--LPLA10002|--1800-00100118|--2102-02003001
       |--1300-00677002|--LPLA10002|--1800-00100118|--2230-05005000
     |--1300-00677002|--LPLA10002|--1800-00200300
       |--1300-00677002|--LPLA10002|--1800-00200300|--2101-04012016
       |--1300-00677002|--LPLA10002|--1800-00200300|--2102-02003001
     |--1300-00677002|--LPLA10002|--1800-00200311
       |--1300-00677002|--LPLA10002|--1800-00200311|--2101-04012016
     |--1300-00677002|--LPLA10002|--1800-00200325
       |--1300-00677002|--LPLA10002|--1800-00200325|--2101-04012016
       |--1300-00677002|--LPLA10002|--1800-00200325|--2230-02002001
     |--1300-00677002|--LPLA10002|--1800-00200409
       |--1300-00677002|--LPLA10002|--1800-00200409|--2101-04012016
       |--1300-00677002|--LPLA10002|--1800-00200409|--2230-02002001
     |--1300-00677002|--LPLA10002|--2101-02001035
 |--4410-TE902C00
 |--6110-11001000

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


6、分析聚合函数

       rank(expr1,expr2,...) within group(order by col1,col2,...)：返回特定數值在統計數值中所佔據的等級,expr1,expr2,...必須為常數
       dense_rank(expr1,expr2,...) within group(order by expr1,expr2,...)：返回特定數值在一組行數據中的等級
       percent_rank(expr1,expr2,...) within group(order by expr1,expr2,...)：返回特定數值在統計數值在統計級別中所占的比例

select rank(1530.100000) within group(order by apa31) rank_level,
       dense_rank(1530.100000) within group(order by apa31) dense_rank_level,
       percent_rank(1530.100000) within group(order by apa31) percent_quto
  from ds.apa_file

--结果：

RANK_LEVEL           DENSE_RANK_LEVEL                PERCENT_QUTO
2254                      1159                                       0.312136326

 

        percentile_cont(percent_expr)within group(order by expr)：返回在計級別中處於某個百分點的特定數值(按照連續分佈模型確定)
        percentile_disc(percent_expr)within group(order by expr)：返回在計級別中處於某個百分點的特定數值(按照離散分佈模型確定)

        first：取得排序等級的第一級，然後使用分組函數匯總該等級的數據,該函數不能夠單獨使用，必須與其它分組函數結合使用
        last：取得排序等級的最後一級，然後使用分組函數匯總該等級的數據,該函數不能夠單獨使用，必須與其它分組函數結合使用

select min(apa35) keep (dense_rank first order by apa31 desc) "AP金額最多等級中的最小已付",
      
       max(apa35) keep (dense_rank first order by apa31 desc) "AP金額最多等級中的最大已付"
  
  from ds.apa_file

--结果：

AP金額最多等級中的最小已付         AP金額最多等級中的最大已付
3500000                                    3500000      

 

        over()視窗函數
           over ([partition by col1] order by col2) ) 表示依col1分組,依col2在分組類排序
           over函數不能夠單獨使用，必須和其它的分組/分析函數配合使用
           over函數不可用在where子句中

--統計2G88與2G60部門2012-04月薪資分配  
select cqk09 姓名,cqk05 部門,cqk30 薪水,  
       sum(cqk30) over(partition by cqk05 order by cqk09) 部門薪水連續加總,  
       sum(cqk30) over(partition by cqk05) 部門薪水總計,  
       100*(round(cqk30/sum(cqk30) over(partition by cqk05),5)) "個人薪水占部門份額%",  
       sum(cqk30) over(order by cqk05,cqk09) 所有薪水連續加總,  
       sum(cqk30) over() 所有薪水總計,  
       100*(round(cqk30/sum(cqk30) over(),5)) "個人薪水占總和份額%"  
  from cqk_file  
  where cqk03=2012 and cqk031 = 4 and cqk05 in ('2G88','2G60')

--结果：

姓名部門薪水部門薪水連續加總部門薪水總計個人薪水占部門份額%所有薪水連續加總所有薪水總計個人薪水占總和份額%
AX2G60330863308613838223.909330863356669.857
BX2G607788411097013838256.28211097033566623.203
CX2G602741213838213838219.8091383823356668.166
DX2G88562735627319728428.52419465533566616.765
SS2G887099412726719728435.98626564933566621.15
IP2G883918716645419728419.86330483633566611.674
DS2G883083019728419728415.6273356663356669.185


--row_number()結合over排序統計2012-04薪資水平中各部門最高的2位  
  -- select cqk09 姓名,cqk05 部門,cqk30 薪水,  
  --  row_number() over(partition by cqk05 order by cqk30 desc) as salary_order    
  --  from cqk_file  
  --  where cqk03=2012 and cqk031 = 4 and salary_order<=2 --此處無法引用salary_order,直接用over運算式也是錯誤的  
  -- 上述SQL段是錯誤的over查詢出來依別名做列也不可以于where子句中使用  
  select cqk09 姓名,cqk05 部門,cqk30 薪水,salary_order 排序   
    from (  
           select cqk09,cqk05,cqk30,  
                  row_number() over(partition by cqk05 order by cqk30 desc) salary_order    
           from cqk_file  
           where cqk03=2012 and cqk031 = 4 and cqk05 in('2G60','2G88')  
         )cqk_temp_file  
    where salary_order <= 2

--结果：

姓名部門薪水排序
BX2G60778841
AX2G60330862
SS2G88709941
DX2G88562732

 

row_number() 、rownum、rank()、dense_rank()區別說明
row_number() over(partition by col1 order by col2)：此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

                                                                                    row_number()和rownum差不多，功能更强一点(可以在各个分组内从1开时排序)

rank()：是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)

dense_rank()：是连续排序,有两个第二名时仍然跟着第三名.相比之下row_number是没有重复值的.

rank()與dense_rank()及row_number()不同點說明

  rank()依薪水排序                        dense_rank()依薪水排序                       row_number()依薪水排序

姓名部門薪水排序 姓名部門薪水排序 姓名部門薪水排序
SI2BSB88881 SI2BSB88881 SI2BSB88881
DI2BSB66662 DI2BSB66662 DI2BSB66662
BP2BSB66662 BP2BSB66662 BP2BSB66663
SP2BSB32324 SP2BSB32323 SP2BSB32324

 

      group_id()：區分分組結果中的重複行
      grouping(expr)：用於確定分組結果是否用到了特定的運算式
                                  返回值0表示用到了該運算式，返回值1表死未用到該運算式
      grouping_id(expr1[,expr2],,,)：用於返回對應于特定行的grouping位向量的值

select apa00,apa13,sum(apa31f),grouping(apa13)   
  from apa_file   
  group by rollup(apa00,apa13)  
  order by apa00,apa13

--结果：

APA00APA13                       SUM(APA31F)     GROUPING(APA13)
11HKD23731714.84              0
11NTD942131              0
11RMB22999119.68              0
11USD2180              0
11     47675145.52              1
12HKD107141459.6              0
12NTD119680              0
12RMB69738785.2              0
12USD92773.7              0
12    177092698.5              1
……                                   …          …

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



--一條SQL查詢出所有程式action中英文說明：
select program as "程式代碼", gaz03 as "程式名稱" ,actionResultList as "程式功能清單"   
 from (select program,substr(max(sys_connect_by_path(action,';')),2) as actionResultList      
        from (select program,action,rank() over (partition by actionList.program order by actionList.action desc)actionOrder  
              from (select gap01 as program,gap02||':'||gbd04 as action from gap_file  
                     left join gbd_file on gap02 = gbd01  
                     where gbd03 = '0'  
                   )actionList  
             )actionOrderList  
       connect by actionOrder-1 = prior actionOrder and program = prior program  
       start with actionOrder = 1  
       group by program  
       order by program  
      )actionReusltTable  
   left join gaz_file on program = gaz01  
   where gaz02 = '0'  
   order by program

--结果：

   程式代碼程式名稱程式功能清單
1aapi001集團代收付基本資料維護作業query:查詢;output:列印;locale:語言;help:幫助….
2aapi010零用金應付帳款系統單據性質維護作業invalid:無效;insert:新增;authorization:使用者設限…
3aapi101應付帳款各期統計開帳作業previous:上筆;next:下筆;modify:更改...
4aapi102應付帳款(依單號)各期統計開帳作業related_document:相關文件;query:查詢…
5aapi103應付帳款系統單據性質維護作業dept_authorization:部門設限;delete:刪除…
6aapi104帳款類別維護作業query:查詢;qbe_select:條件查詢;qbe_save:條件儲存…
7aapi105留置原因維護作業query:查詢…
8aapi108統一編號資料維護作業exit:結束;delete:刪除;controlz:必要欄位;controlg:開窗查詢…
9aapi111零用金應付帳款各期統計開帳作業previous:上筆;next:下筆;modify:更改insert:新增;delete:刪除…

 

 

統計'Tri-Net','AMBER -W','Belkin-C','GTC','SAN SHIN'客戶各月份接單金額

要求輸出格式為

接單月份

Tri-Net

AMBER -W

Belkin-C

GTC

SAN SHIN

2011/08

接單金額

接單金額

接單金額

接單金額

接單金額

….

….

….

with cte_oea_file as  
(select substr(to_char(oea02,'YYYY/MM/DD'),1,7) po_date,oea032 po_custom,sum(oeb14) po_sales  
   from oea_file join oeb_file on oea01 = oeb01  
   where oea032 in ('Tri-Net','AMBER -W','Belkin-C','GTC','SAN SHIN')  
   group by substr(to_char(oea02,'YYYY/MM/DD'),1,7),oea032  
  order by 1,2  
)  
select po_date "接單月份",  
   sum(decode(po_custom,'Tri-Net',po_sales,0))  "Tri-Net",  
   sum(decode(po_custom,'AMBER -W',po_sales,0)) "AMBER -W",  
   sum(decode(po_custom,'Belkin-C',po_sales,0)) "Belkin-C",  
   sum(decode(po_custom,'GTC',po_sales,0)) "GTC",  
   sum(decode(po_custom,'SAN SHIN',po_sales,0)) "SAN SHIN"  
 from cte_oea_file  
 group by po_date  
 order by po_date
接單月份            Tri-Net       AMBER -W         Belkin-C          GTC          SAN SHIN
2011/08 768796.48 161573.68 14162.5 319450 10250.96
2011/09 566008.26 221550.9 258366.5 268070 8920
2011/10 270341.68 76241.95 147402 85850 9020
2011/11 464575.39 0 284704 469675 15040
2011/12 295007.55 0 140446.2 918000 21250
2012/01 70835.5 0 0 274850 5100
2012/02 148424.53 0 1425 235255 40690
2012/03 179333.31 0 0 638550 55596.4
2012/04 158162.51 0 0 212000 10110
2012/05 0 0 0 220600 30160



http://blog.csdn.net/yihuiworld



李yi辉
关注
  • 8
    点赞
  • 踩
  • 66
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
oracle查询是否包含英文字符串,oracle字符串载取及判断是否包含指定字符串
weixin_42405368的博客
04-04 1328
切割字符串，一般是不会通过数据库函数来处理的(大部分是查询出结果后通过代码处理的)，但是也有特殊的情况；比如：Oracle数据库有一张表Node，字段有：ID：主键parentID：父节点STATID：查询编码NAME：名称STATNAME：查询名字SCALE：父子级别看的出，这是张存储父子结构关系的表，而STATID的左右就是把父子结构的关系拼接起来：比如，某记录为：IDparentIDST...
sql 判断字符串中是否包含数字和字母的方法
09-10
主要介绍了sql 判断字符串中是否包含数字和字母的方法,这是一个比较实用的方法，需要的朋友可以参考下
Oracle_SQL_内置函数大全
12-09
PPT供参考学习
SQL字符串截取、反转、替换、查找等处理操作
qq_41894426的博客
10-24 2666
sql 截取字符bai串： 1、LOCATE(substr , str )：返回子串 substr 在字符du串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0； 2、POSITION(substr IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同； 3、LEFT(str, length)：从左边开始截取str，length是截取的长度； 4、RIGHT(str, len
sql 查询字段是中文/英文/数字 正则表达式
weixin_34246551的博客
01-22 2511
一、包含中文字符 select * from 表名 where 列名 like '%[吖-座]%' 二、包含英文字符 select * from 表名 where 列名 like '%[a-z]%' 三、包含纯数字 select * from 表名 where 列名 like '%[0-9]%' 　 上面的正则表达式，只能用like选出该字段中含有中...
oracle 查出字段中含有英文的记录的sql
zwxiaole的专栏
10-09 1708
select * from dim_swjg j where translate(trim(j.swjg_id), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', ' ') &lt;&gt; trim(j.swjg_id) ;  
SQL 查询含中英文的语句
Homer.Wu的成长之路
03-14 1726
select * from table where a like '%[吖-座]%'  查询表中a字段中所有含中文的数据 select * from table where a like '%[a-z]%' 查询表中a字段中所有含英文的数据
oracle 中文 英文 判断,SQL判断某列中是否包含中文字符或者英文字符
weixin_30070961的博客
04-13 570
设计模式 之 装饰者(Decorator)模式装饰者模式(Decorator):动态地为一个对象添加一些额外的职责,若要扩展一个对象的功能,装饰者提供了比继承更有弹性的替代方案. 结构图: 抽象构件类(Component):给出一个抽象的接口,用 ...亚马逊ec2服务器上无法使用sudo执行npm命令的解决办法sudo ln -s /usr/local/bin/node /usr/bin/nod...
regex
IvwvI的专栏
02-03 290
ORACLE中的支持正则表达式的函数主要有下面四个： 1，REGEXP_LIKE ：与LIKE的功能相似 2，REGEXP_INSTR ：与INSTR的功能相似 3，REGEXP_SUBSTR ：与SUBSTR的功能相似 4，REGEXP_REPLACE ：与REPLACE的功能相似 它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，...
Oracle SQL 内置函数总结
03-16
NULL 博文链接：https://lauvenman.iteye.com/blog/427946
Oracle SQL 内置函数大全
06-07
Oracle SQL 内置函数大全 SQL中的单记录函数 给出整数,返回对应的字符 连接两个字符串 增加或减去月份 用于对查询到的结果进行排序输出
Oracle SQL 内置函数以及解析
12-08
Oracle SQL 内置函数以及解析
Oracle sql内置函数
10-13
Oracle sql内置函数,便于新手有效的快速查询。
Oracle SQL：update更新语句总结
热门推荐
李艺辉的专栏
03-28 12万+
Oracle update SQL 语句总结: update 用于修改表中的数据
Oracle SQL：delete删除语句总结
李艺辉的专栏
03-27 8万+
Oracle delete sql 语句总结：1、delete 用于删除表中的某行或整个数据表中的数据;2、 truncate也可用于删除整个表里面的数据,truncate释放空间,速度更快
Oracle SQL：select各类查询语句总结
李艺辉的专栏
03-28 3万+
Oracle select SQL 各类查询语句总结
Oracle SQL：insert into语句总结 与 merge into用法总结
李艺辉的专栏
03-27 3万+
insert into语句总结 vs merge into用法总结：1、insert into 插入数据;2、merge into 有则更新,无则插入;
Oracle SQL语句分类
李艺辉的专栏
03-27 3861
Oracle SQL(Structured Query Language)即结构化查询语句主要分为四类： 1、DML(Data Mannipulation Language)数据操纵语言：查询、操纵数据表资料行; 2、DDL(Data Definition Language)数据定义语言：建立、修改、删除数据库中数据表对象; 3、DCL(Data Control Language)数据控制语言：用于执行权限授予与收回操作; 4、 TCL(Transactional Control Language)事物控制
oracle里在哪查看内置函数
最新发布
06-13
在Oracle数据库中，您可以通过访问...此外，您也可以在Oracle SQL Developer等数据库管理工具中查看内置函数的完整列表和详细信息。要查看内置函数列表，请在SQL Developer中打开SQL工作区，然后展开“函数”选项卡。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值