oracle语法【1.求两个时间的时间差 小时】ROUND(TO_NUMBER(to_date(ORDER_INVALID_TIME,'yyyy-mm-dd hh24:mi:ss')-to_date(ORDER_INVALID_TIME,'yyyy-mm-dd hh24:mi:ss')))天：ROUND(TO_NUMBER(etime-stime))小时：ROUND(TO_NUMBER(etime-stime)*24)分：ROUND(TO_NUMBER(etime-stime)*24*60)秒：ROUND(TO_NUMBER(etime-stime)*24*60*60)例：select ROUND(TO_NUMBER(to_date(to_char(ORDER_INVALID_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')- to_date(to_char(ORDER_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*24) from ec_order o【2.精确到年月日】trunc(time) between :v_stime and :v_etime
【3.百分比，除数不能为0，保留两位小数】to_char(审批及时订单/nvl(decode(订单数,0,'1',订单数),1)*100,'FM9999999999990.00')||'%' as 审批及时率【4.条件 和if else if else 同理】case when a>=90 then '优秀' when a<90 and a>70 then '良'when a=60 then '合格'else '不合格'end(case when TO_NUMBER(trunc(o.ORDER_TIME)-trunc(requisition.ZQG_DATE))>1 then '延时审批' else '及时审批' end)
【5.排序 和 分组】排序：order by 列名 desc(倒序) asc（升序）分组：GROUP BY 列名例：select ,订单数,审批及时订单,to_char(审批及时订单/nvl(decode(订单数,0,'1',订单数),1)*100,'FM9999999999990.00')||'%' as 审批及时率,审批平均用时 from(select zc.ZCOMPANYMS as ,(select count(eo.ORDER_ID) from EC_ORDER eoleft join EC_ORDER_PURCHASE p on eo.ORDER_ID=p.ORDER_IDwhere p.ZCOMPANYCODE=zc.ZCOMPANYCODE) as 订单数,(select count(eo.ORDER_ID) from EC_ORDER eoleft join EC_ORDER_PURCHASE p on eo.ORDER_ID=p.ORDER_IDleft join EC_PURCHASE_REQUISITION r on p.pr_id=r.pr_idwhere p.ZCOMPANYCODE=zc.ZCOMPANYCODEand (TO_NUMBER(trunc(eo.ORDER_TIME)-trunc(r.ZQG_DATE))<1)) as 审批及时订单,(to_char(avg(ROUND(TO_NUMBER(to_date(to_char(o.ORDER_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')-to_date(to_char(requisition.ZQG_DATE,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*24)),'FM9999999999990.00')) as 审批平均用时from Z_COMPANYCODE zcleft join EC_ORDER_PURCHASE eopurchase on eopurchase.ZCOMPANYCODE=zc.ZCOMPANYCODEleft join EC_ORDER o on o.ORDER_ID=eopurchase.ORDER_IDleft join EC_PURCHASE_REQUISITION requisition on eopurchase.pr_id=requisition.pr_idwhere o.BIZ_CODE='B050'and (trunc(o.ORDER_TIME) between :v_stime and :v_etime)GROUP BY zc.ZCOMPANYMS,zc.ZCOMPANYCODE)【6.列为空时，赋值为0】nvl(列名,0)to_char(nvl(eop.PROD_ORIPRICE,0),'FM999999999999990.00')【7.合并两个sql语句】union allselect * from(select a.* from a where a.status='01'union allselect a.* from a where a.status='02') where a.status=?