函数

----聚合函数，操作多行数据，并返回一个结果
select avg(aly_layer) as 平均值 from t_aly_il_result
select sum(aly_layer) as 总和 from t_aly_il_result
select min(aly_layer) as 最小值 from t_aly_il_result
select max(aly_layer) as 最大值 from t_aly_il_result
select count(aly_layer) from t_aly_il_result
 

select CREATE_TIME, avg(aly_layer), count(*)
from t_aly_il_result
where aly_layer > 0
group by CREATE_TIME

----字符函数
SELECT INSTR('ORACLE技术圈','技术') FROM DUAL; --查找字符串X中字符串Y的位置
select CONCAT('ORACLE','技术圈') from DUAL; ---连接2个字符串
select LENGTH('ORACLE技术圈')from DUAL;---求字符串长度
SELECT LOWER('ORACLe技术') FROM DUAL;---大写转为小写
SELECT UPPER('Oracle技术圈') FROM DUAL;---小写转大写
SELECT INITCAP('ORACLE is good ') FROM DUAL;---首字母为大写
SELECT LTRIM('--ORACLE技术圈','-') FROM DUAL; --去掉字符1左边的字符2，即去掉-（2不填写默认为空格）
SELECT RTRIM('ORACLE技术圈--','-') FROM DUAL;--去掉字符1右边的字符2，即去掉-（2不填写默认为空格）
SELECT TRIM('--ORACLE技术圈--','-') FROM DUAL;--去掉字符1两边的字符2，即去掉-（2不填写默认为空格）
SELECT REPLACE('ORACLE技术圈1','技术圈1','技术交流') FROM DUAL;---把1中的2替换为3
SELECT SUBSTR('ORACLE技术圈',1,6) FROM DUAL;---从第一位开始截取，截取到第六位
SELECT RPAD('ORACLE',9,'-') from DUAL;---对ORACLE右补-，直到长度为9
SELECT LPAD('ORACLE',9,'-') from DUAL;---对ORACLE左补-，直到长度为9

 
---日期函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;---系统时间
SELECT YEAR(start_time) from t_md_inst
select systimestamp from dual;---包含时区信息，精确到微秒
select sysdate,last_day(sysdate) from dual;---返回指定日期当月的最后一天
SELECT SYSDATE,add_months(SYSDATE,4) FROM dual;---系统时间+几个月
SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;---四舍五入到最近的周的周日
SELECT SYSDATE "date",

EXTRACT(YEAR FROM SYSDATE)"year",

EXTRACT(MONTH FROM SYSDATE)"month",
EXTRACT(DAY FROM SYSDATE)"day",

EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",

EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",

EXTRACT(SECOND FROM SYSTIMESTAMP)"second"

FROM dual;----提取日期中的部分（年、月、日、时、分、秒）

 

--数值函数
select trunc(7.816, 2), trunc(7.816), trunc(76.816, -1) from dual;--y不填时，默认为y=0;
select ABS(-3) from dual---绝对值
select ACOS(1) from dual ----反余弦
select COS(1) from dual ----余弦
select CEIL(5.4) from dual ----大于或等于X的最小值
select FLOOR(5.8) from dual ----小于或等于X的最大值
select LOG(2，4) from dual ----X为底Y的对数
select MOD(8，3) from dual ----X除以Y的余数
select POWER(2，3) from dual ----X的Y次幂
select ROUND(3.456，2) from dual ----整数时.往右边第Y位四舍五入，-2时.往左边第Y位四舍五入
select SQRT(4) from dual ----X的平方根
select TRUNC(3.456，2) from dual ----X在第Y位截断
select ROUND(351.654,-2) from dual

---转换函数
1、把一个字符串以fmt格式转换成一个日期类型
select to_date('2018-11-13','yyyy-mm-dd') from dual;--把字符串x按照格式f进行格式化转换为时间类型

2、把日期和数字转换为字符串
select to_char(123.46,'999.9') from dual;--把字符串或时间类型x按格式f进行格式化，转换为字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;
SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

3、把一个字符串以fmt格式转换为一个数字
select to_number('123.74','999.99') from dual--把字符串x按照格式f进行格式化转换为数值类型
select bin_to_num(1,0,0) from dual;---二进制转换为十进制

select cast('123' as number) num,----数据类型转换，用于数字，字符，时间类型转换
cast(123 as varchar2(3)) as ch,
cast(to_date('20181112', 'yyyymmdd') as varchar2(12)) as time
from dual;

 

---其它函数
NVL(X,VALUE) ---如果X为空，返回value，否则返回X，例如：如果没发奖金，每人奖金100元（奖金为空，返回100）
NVL2(x,value1,value2)---如果x非空，返回value1，否则返回value2,例如：如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元
NULLIF(exp1, exp2)---如果表达式exp1与exp2的值相等返回null,否则返回exp1的值
COALESCE(exp1, exp2, ...)---依次考察各参数表达式,遇到非null值即停止并返回该值
SELECT least(10,32,'123','2006') FROM dual;---返回表达式列表中值最小的一个
SELECT greatest(10,32,'123','2006') FROM dual;--返回表达式列表中值最大的一个

---case
select empno，ename, sal,
case deptno when 10 then '财务部'

  when 20 then '研发部'

  when 30 then '销售部'

  else '未知部门'

end 部门
from emp

---decode
select empno, ename, sal,
       decode (deptno, 10, '财务部',
                      20, '研发部',
                     30, '销售部',
                '未知部门')
     部门
from emp;

---行转列
select *
from (select a.dept_name, a.id, c.scy_lvl, b.prem_codes-- DEPT_NAME、 ID、 SCY_LVL（行转列）、PREM_CODES（统计行对应字段的最大值）
from T_DA_MGR_ORG a
left join T_DA_ORG_SCY_REL b
on a.id = b.dept_id
left join T_DA_SCY_LVL c
on b.scy_lvl = c.scy_lvl)
pivot(max(prem_codes）--pivot（聚合函数（销售额））---（最大值、行数、总和、平均值)
for scy_lvl in(1 as syslvl1, 2 as syslvl2, 3 as syslvl3, 4 as syslvl4))-----for 商品类型 in (行类型1 as 行转列的列名称1，行类型2 as 行转列的列名称2）
where 1 = 1;

---eg
select *
from T_DA_ORG_SCY_REL
pivot(max(prem_codes)
for scy_lvl in(1 as cs1, 2 as cs2, 3 as cs3, 4 as cs4))

select *
from T_DA_ORG_SCY_REL
unpivot
(scy_lvl for prem_codes in ('1','2','3','4'))

SELECT * FROM （数据查询集）
PIVOT
(
SUM(Score/*行转列后 列的值*/) FOR
coursename/*需要行转列的列*/ IN (转换后列的值)
)

---列转行---unpivot (多列变一列的列值取别名 for 多列变1列的列取别名 in (多列字段名称)
select projectname, supplier, supplysum //与下面别名相同
from projectdetail unpivot(supplysum //为列转行中多列变成一列的 值取别名
for supplier //在结果中成为一列的原多列取统一别名
in(overseasupply,
nativeSupply,
southsupply,
northsupply))
--eg a
SELECT COLUMN_ID, COLUMN_NAME, COLUMN_DATA---多列的列名、多列的列值
FROM T_CREATE_TABLE_1 UNPIVOT(COLUMN_DATA FOR COLUMN_NAME IN(COLUMN_1,
COLUMN_5));