1、extract函数取日期的年月日 时分秒
Select Extract(month from sysdate)as year from dual ;
–取得当前的月份
–取得当前的月份 可以相应的通过month day year取得月份 日子和年份
select extract(month from timestamp’2011-11-12 12:12:12’) month from dual;
–取得某个表中的某个字段的月份
select extract(month from to_timestamp(tt.AUDIT_DATE,‘yyyy-mm-dd hh24:mi:ss’)) month from sys_user tt ;
–可以相应的变为minute hour
select extract(second from timestamp’2011-11-12 12:12:12’) seconde from dual;
–获取两个日期之间的具体时间间隔,extract函数是最好的选择
select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
,extract(second from dt2-dt1) second
from (
select to_timestamp(‘2011-02-04 15:07:00’,‘yyyy-mm-dd hh24:mi:ss’) dt1
,to_timestamp(‘2011-05-17 19:08:46’,‘yyyy-mm-dd hh24:mi:ss’) dt2
from dual)
/
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
102 4 1 46
2、oracle 分组后取每组第一条数据
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,
test1.*
FROM test1)
WHERE rn = 1 ;
例如:
select * from (
SELECT t.id,
t.attribute_03,
t.weight_version,
ROW_NUMBER() OVER(PARTITION BY t.attribute_03 ORDER BY t.weight_version desc) rn
FROM BIBSC_JX_KPI_ASSESSMENT_PLAN t
where t.period_code is not null)
where rn = 1
3、oracle 的round函数: 查询时,确定对数据的四舍五入;
select round(number,index) from dual;
number:需要处理的数据;
index:需要保留的小数位数
select round(123.3423423,5) from dual;
4、oracel函数decode用法:
decode(value,if1,then1,if2,then2,if3,then3,…,else);
如果value==if1,decode函数结果为then1,…,如果不等于任何一个值,则返回else
示例1如下:
select decode(1,1,‘a’,‘b’) as a from dual;
如果1=1(第一个参数等于第二个参数),则decode函数取值为a;
如果1!=1(第一个参数不等于第二个参数),则decode函数取值为b;
示例2如下:
select decode(1,2,‘a’,decode(1,2,‘b’,‘c’)) as a from dual;
如果1=2,decode函数取值为a;
如果1!=2,decode函数取值为decode(1,2,‘b’,‘c’);递归函数中接着进行判断,如果1=2,函数取值b,如果1!=2,函数取值c;
示例3如下:分析同上
select decode(1,2,‘a’,decode(1,2,‘b’,decode(1,2,‘c’,‘d’))) as a from dual;
select decode(‘a’,‘1’,‘true’,‘false’) as flag from dual;
5、trunc函数
select sysdate from dual; --2018/7/17 20:05:00
select trunc(sysdate) from dual; --2018/7/17
6、oracle创建一个函数,返回2个参数
CREATE OR REPLACE FUNCTION F_getPeriodStr(F_id IN VARCHAR2) RETURN VARCHAR2 IS
begin_str VARCHAR2(400);
end_str VARCHAR2(400);
var_return VARCHAR2(400);
BEGIN
select attribute_01,attribute_02 into begin_str,end_str from risk_zl_period where id = F_id;
WHILE begin_str <= end_str LOOP
if var_return is null then
var_return := begin_str;
begin_str := begin_str + 1;
else
var_return := var_return ||','|| begin_str;
begin_str := begin_str + 1;
end if;
END LOOP;
RETURN var_return;
END F_getPeriodStr;
7、 round(number1,number2); 保留指定的小数位数
注意:round函数中,第一个参数是要查询的数据,第二个参数为查询的这个数据保留几位小数;
select round(12.3567,2) from dual; 查询结果为12.35
8、nvl(param1, param2)
表示,如果第一个值param1为空,则取第二个值param2
select round(nvl(t.RISK_VALUE, t.EVAL_VALUE),2) as RISK_VALUE
from pt6.RISK_FRAME_CASE_REL_TJ_V t
where t.risk_data_id = ‘ff808081611d1ec701611d451e4f07d2’ ;
9、sql:行转列 wm_concat(列名);
函数可以把列值以","号分隔起来,并显示成一行
例如:
select wm_concat(dept_id) as dept_id from (
select t.attribute_02 as dept_id from bibsc_jx_kpi_condition t where t.attribute_03 in (
select t.id from bibsc_jx_kpi t where t.parent_id = ‘8a58c6745e7ffd6e015e803a89ae07c7’
)
union
select t1.kpi_dept_range as dept_id from bibsc_jx_kpi_rule t1 where t1.bibsc_jx_kpi_parent_id in (
select t.id from bibsc_jx_kpi t where t.parent_id = ‘8a58c6745e7ffd6e015e803a89ae07c7’
)
) a
10、case when then else end 语法用法
select t1.id,
t1.parent_id,
t1.period_id,
t1.zl_kpi_id,
t1.attribute_02,
m.attribute_03 as kpi_Sskj,
case
when t1.attribute_02 = ‘data_type’ then
t1.zl_kpi_id
else
rz.kpi_name
end as kpiName
from RISK_ZL_KPI_YUCE t1
left join risk_zl_kpi_monitor_data m
on t1.zl_kpi_id = m.id
left join RISK_ZL_KPI_LIBS_TREE rt
on m.risk_zl_kpi_monitor_id = rt.id
left join RISK_ZL_KPI_LIBS rz
on rt.kpi_lib_id = rz.id
WHERE t1.RISK_STATE like ‘%’ || ‘1’ || ‘%’
order by t1.creation_date desc
===============================================================================================
注意case when then else end 语法用法
case when t1.attribute_02 = ‘data_type’ —当attribute_02等于data_type时,取t1.zl_kpi_id作为kpiName的值,
then t1.zl_kpi_id ----否则的话,rz.kpi_name作为kpiName的值;
else rz.kpi_name
end as kpiName
============================================================================================
select a.*,
case
when risk_value >= 12 then
‘red’
when risk_value <= 6 then
‘green’
else ‘yellow’
end as risk_leva
from a
============================================================================
case when a=1 then ‘1’
when a=2 then ‘2’
when a=3 then ‘3’
else ‘4’
end as result_flag
from dual;
============================================================================================
with a as(
select r.*,t.first_name,t.secod_name from RISK_EVENT_RESULT_LEVEL r
left join RISK_EVENT_FRAME_EFFICT_TYPE t on r.effect_type_id = t.id
) select a.*,
case when first_name = '运营'
then (first_name ||'-'|| secod_name)
when first_name = '财务'
then (first_name ||'-'|| secod_name)
else first_name
end as effect_Type_name
from a;
11、pivot的用法
–使用前数据集形式:
jx_dept jx_period jx_value
测试部门C 2017年4月 58
测试部门D 2017年4月 44
测试部门E 2017年4月 84
测试部门F 2017年4月 54
--使用后数据集形式:
jx_dept 2017年4月
测试部门E 84
测试部门C 58
测试部门F 54
测试部门D 44
sql如下:
with a as
(select t1.jx_dept, t1.jx_period, t1.jx_value
from bibsc_center_jxtj t1
WHERE t1.JX_ORDER_TYPE = ‘DEPT’
and t1.JX_DEPT_TYPE = ‘职能部门’
and t1.JX_PERIOD in (‘2017年4月’))
select * from a pivot(sum(jx_value) for jx_period in(‘2017年4月’))
12、 instr(a,b)
select * from a where instr(a,b)>0;用于实现B字段是A字段中的某一部分的时候,要论顺序或者要相邻的字符。
如果想要不论顺序或者不相邻的字符时,定义函数可以实现:
select * from a where instr(a,b)>0;这个只能实现B字段是A字段中的某一部分的时候。
如果想要不论顺序或者不相邻的字符时,定义函数可以实现
create or replace function checks(v_a varchar2,v_b varchar)
return number
as
num number;
cou number;
begin
num := -1;
cou:=0;
for i in 1…length(v_b) loop
if instr(v_a,substr(v_b,i,1))>0 then
cou:=cou+1;
end if;
end loop;
if cou=length(v_b) then
return cou;
end if;
dbms_output.put_line(cou||’ '||length(v_b));
return num;
end;
结果:
SQL> select * from a;
A B
---------- ----------
asdf sd
asdf ad
asdf df
asdf asdf
SQL> select * from a where checks(a,b)>0;
A B
---------- ----------
asdf sd
asdf ad
asdf df
asdf asdf
13、oracle中,查询多列中的最大值和最小值
greatest和least函数
select id , chinese , math , english , greatest( chinese ,math ,english) as max , least( chinese , math ,english) as min from table;
例如:
select greatest(1,2,3,4) as max , least( 5,6,7,8) as min from dual;
14、oracle函数中有不足位数补空格的函数
LPAD和RPAD分别是左补位和右补位
repalce(lpad(字段名,共几位),‘ ’,‘0’)
如运行
select replace(lpad(‘123’,5),’ ',‘0’) from dual
结果是00123
即补足五位
select lpad(‘123’,5,‘0’) from dual 这样也可以