不定时更新自己用到的oracle 比较有用的sql语句和函数用法

今天做根据季度统计的功能，要实现这个功能，需要知道当前月份在第几个季度。这里日期用sysdate来代替数据库里面的日期字段。

首先要得到月份月份在第几季 select to_char(sysdate,'Q') from dual;第一季度是1,第二季度是2.......

然后根据季度的值来显示第几季度。这里用到decode函数。首先看看decode的含义

含义解释：
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

最后的sql如下：select decode(to_char(sysdate,'Q'),'1','第一季度','2','第二季度','3','第三季度','4','第四季度') from dual

在实际应用中就大概是select decode(to_char(经过转换后的你的日期,格式为YYYY-MM-DD,'Q'),'1','第一季度','2','第二季度','3','第三季度','4','第四季度') from 你的表名

2.需要统计超标率，也就是两个字段相除得到百分比,先上sql,在项目里大概是这样的

select cast((你的字段1/你的字段2)*100 as decimal(18,2))||'%' from dual    两个字段相除，取小数点后面两位数，然后再拼接上百分号。当然，需要注意除数不能为0。

这里写一下cast的用法，官方文档

CAST

CAST converts values from one data type to another.

Return Value

The data type specified by type_name.

Syntax

<span class="bold" style="font-weight: bold;">CAST</span>(expr <span class="bold" style="font-weight: bold;">AS</span> type_name)

<span style="font-family: Tahoma, sans-serif; font-weight: bold; font-size: 16px; background-color: rgb(255, 255, 255);">Arguments</span>

expr can be an expression in one of the data types.

type_name is one of the data types listed in Table1-2，"Dimensional Data Types".

Table 3-1 shows which data types can be cast into which other built-in data types. NUMBER includes NUMBER, DECIMAL, and INTEGER. DATETIME includes DATE,TIMESTAMP, TIMESTAMP WITH TIMEZONE, and TIMESTAMP WITH LOCAL TIMEZONE. INTERVAL includes INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH.

Table 3-1 Compatible Data Types

From	To BINARY_FLOAT, BINARY_DOUBLE	To CHAR, VARCHAR2	To NUMBER	To DATETIME, INTERVAL	To NCHAR, NVARCHAR2
BINARY_FLOAT, BINARY_DOUBLE	yes	yes	yes	no	yes
CHAR, VARCHAR2	yes	yes	yes	yes	no
NUMBER	yes	yes	yes	no	yes
DATETIME, INTERVAL	no	yes	no	yes	yes
NCHAR, NVARCHAR2	yes	no	yes	no	yes

通过官方文档，cast可以把一种类型的转换为另一种类型。但是某些类型之间是不可以转换的，就是表3-1里面的no.

cast(expr as type_name) expr可以是表达式，数据库字段等，type_name是要转换成的类型。

3、点击合计值，查看明细

一般需要统计的基本都是数值。所以就以数值为例吧。

比如统计某个项目的人员总共的出差天数，点击合计值，展示项目的明细，包括有哪些人员等等。这里需要用到分组函数

select 字段一,字段二,字段三,字段四,字段五  from 表名 [可以加上where条件] group by 字段一,字段二,字段三,字段四,字段五 having sum(要统计的字段，比如天数)=10,

这样基本就能实现点击汇总查看明细了。

4、关于日期的一些计算，这些经常用到。这里就不写比较常见的了，总结了几个比较有难点的sql

得到一年中的所有的天数

select trunc(to_date('2016-12-31','yyyy-mm-dd'),'yyyy')+rownum-1 as aaa from dual connect by rownum<=(to_date('2016-12-31','yyyy-mm-dd')-to_date('2016-01-01','yyyy-mm-dd')+1)

得到一年有多少天

select (add_months(trunc(sysdate,'yyyy'),12)-1)-trunc(sysdate,'yyyy')+1 from dual;

得到今天是一周中的第几天，如果是第一天，则为周日，以此类推，第七天是周六

select to_char(to_date('2016-10-22','yyyy-mm-dd'),'d')from dual;

5、oracle截取以逗号分隔的字符串。比如一个字符串 'a,b,c'，现在想分别得到a,b和c

select regexp_substr('a,b,c','[^,]+',1,rownum) from dual connect by rownum<=length('a,b,c')-length(replace('a,b,c',',',''))+1;