trunc() instr() substr()

本文详细介绍了 Oracle 数据库中常用的 SQL 函数,包括 trunc() 截取时间与数字的方法、instr() 在字符串中查找子串及其与 like 子句的区别、substr() 字符串截取功能,以及 nvl() 判断空值的用法。通过具体示例帮助读者更好地理解和应用这些函数。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 trunc():    截取的意思

    1、截取时间   最终的显示形式都是:年月日时分秒 2016-12-15 00:00:00

    select trunc(sysdate ,'yyyy') from dual ;今年第一天,零点

    select trunc(sysdate ,'yy') from dual ;   今年第一天,零点

    select trunc(sysdate ,'mm') from dual ;这个月第一天,零点

    select trunc(sysdate ,'dd') from dual ;今天,零点

    select trunc(sysdate ) from dual ;今天,零点

    select trunc(sysdate ,'d') from dual ;这个星期第一天,零点

    select trunc(sysdate ,'hh') from dual ;当前时间,精确到小时

    select trunc(sysdate ,'mi') from dual ;当前时间,精确到分钟

    2、截取数字

    trunc(a,b);   a:表示要操作的数     b:表示要保留的小数位数


如下给出一个例子


计算得到相差的天数


 instr():     一般有两种用法:

     1、从一个字符串中查找指定子串的位置

     select instr('abcdeab','a') position from dual; 

     显示结果为1 (说明是从1开始查找a的位置的)

     select instr('abcdeab','a',2) position from dual;

     显示结果为6  从第二个位置开始查找a

     select instr('abcdeab','b',2,2) position from dual;

     显示结果为7  从第二个位置开始查找第二次出现b的位置

     select instr('abcdeab','f') position from dual;

     显示结果为0  找f在字符串中的位置,如果找不到返回结果为0


    2、如下格式时有  like  的作用

    select * from tableA where instr(NAME,'张三')>0;

    等同于

    select * from tableA where name like '%张三%';

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

    select * from tableA where instr(NAME,'张三')=1;

    等同于

    select * from tableA where name like '张三%';

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

    select * from tableA where instr(NAME,'张三')=0;

    等同于

    select * from tableA where name not like '%张三%';

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

   select *  from tableA where instr('张三, 李四', name) > 0; 

   等同于 

   select *  from tableA where name like '%张三%' or name like '%李四%'; 


    3、instr()和like 两者效率问题

    简单的检索两者效率相当,复杂的检索instr()效率比like高,所以可以选择instr()函数来替换like使用



 substr():用来截取字符串

                   select substr('abcdeab',0,2 ) from dual;

         结果为:ab      表示从第一位开始截取2位,此处第一位也是从1开始

         select substr('abcdeab',1,2 ) from dual;

         结果为:ab      表示从第一位开始截取2位,此处第一位也是从1开始

         select substr('abcdeab',3,10 ) from dual;

         结果为:cdeab     从第三位开始截取,如果超出范围,那么到最后一位截止


nvl():判断是否为空

nvl(table.money, 0): 表示如果表中的money字段如果为空,则赋值为0,如果不为空则为本身的值






        
<select id="getWeeklyData" resultType="com.huawei.it.tgmes.tmas.weeklyplanaccuracy.entity.bo.BaseWeeklyPlanAccuracy"> With temp1 As ( Select distinct to_char(current_timestamp, 'yyyyMMdd') As batchNo, to_char(trunc(wwi.plan_online_time,'IW'),'yyyy-MM-dd') As week, to_char(trunc(wwi.plan_online_time,'IW'),'yyyyMMdd') As weekjoin, wwi.plan_online_time As planOnLineTime, wwi.workorder_name As workOrderName, wwi.state_Type As stateType, case when wwi.manufacture_mode ='ODM' then 'ODM' when substr(wwi.mfg_site_name,1,2)='华为' then '自制' when wwi.manufacture_mode='EMS' then 'EMS' end As manufactureMode, wwi.item_code As itemCode, wwi.work_serction As workSerction, wwi.work_serction As prodItemType, wwi.prod_model_code As prodModelCode, wwi.release_time As releaseTime, wwi.factory_code As factoryCode, bfi.factory_name As factoryName, wwi.workorder_qty As planQty, bpi.prodLineCode, bpi.prodLineCnName, bpi.prodLineEnName, bpi.prodSeriesCode, bpi.prodSeriesCnName, coalesce(wpc.prod_convert_name, wpc.prod_series_en_name) As prodSeriesEnName From ( Select distinct wf.plan_online_time,wf.workorder_name,wf.state_Type,wf.item_code,wf.work_serction, wf.release_time,wf.factory_code,wf.workorder_qty,wf.prod_model_code,wp.manufacture_mode,wp.mfg_site_name From T_WAVE_WORKORDER_INFO wf inner join T_EBC_PROD_INSTR wp on wf.workorder_name = wp.prod_instr_no and wp.manufacture_mode is not null and wp.manufacture_mode <![CDATA[<>]]> '' and wp.manufacture_mode <![CDATA[<>]]> 'NA' Where wf.state_Type In (3, 12, 4) And (substring (wf.workorder_name,3,1) = 'Z' or substring (wf.workorder_name,3,1) = '1') <![CDATA[ And wf.plan_online_time >= TO_TIMESTAMP(#{startTime,jdbcType=VARCHAR}, 'yyyy-mm-dd hh24:mi:ss') And wf.plan_online_time <= TO_TIMESTAMP(#{endTime,jdbcType=VARCHAR}, 'yyyy-mm-dd hh24:mi:ss') ]]> ) wwi Left Join T_BASE_WORKORDER_PROD_CONVERT wpc On wpc.prod_model_code = wwi.prod_model_code Left Join ( Select prod_line_code As prodLineCode, prod_line_cn_name As prodLineCnName, prod_line_en_name As prodLineEnName, prod_series_code As prodSeriesCode, prod_series_cn_name As prodSeriesCnName, prod_series_en_name As prodSeriesEnName, prod_model_code As prodmodelcode From T_BASE_PROD_CATALOG Group By prod_line_code, prod_line_cn_name, prod_line_en_name, prod_family_code, prod_family_cn_name, prod_family_en_name, prod_series_code, prod_series_cn_name, prod_series_en_name, prod_model_code, prod_model_cn_name, prod_model_en_name) bpi On wwi.prod_model_code = bpi.prodmodelcode Left Join t_Base_Factory_Info bfi On wwi.factory_code = bfi.factory_code), temp2 As ( Select batchNo, week, weekjoin, sum(planQty) As workOrderQty, prodItemType, factoryCode, factoryName, prodSeriesEnName, prodLineCode, prodLineCnName, prodLineEnName, prodSeriesCode, prodSeriesCnName, manufactureMode From temp1 Group By (batchNo, week, weekjoin, prodItemType, factoryCode, factoryName, prodSeriesEnName, prodLineCode, prodLineCnName, prodLineEnName, prodSeriesCode, prodSeriesCnName, manufactureMode) ), temp3 As ( Select SUBSTR(T.tmas_version, 6, 13) As weekjoin, t.tmas_version, t.factory_code, t.prod_series_en_name, t.produce_type_cn, sum(t.qty) As systemLockQty From t_base_two_week_lock t Where t.item = '系统互锁量' Group By t.factory_code, t.prod_series_en_name, t.produce_type_cn, t.tmas_version ) Select t2.*, t3.systemLockQty From temp2 t2 Join temp3 t3 On t2.weekjoin = t3.weekjoin And t2.prodItemType = t3.produce_type_cn And t2.factorycode = t3.factory_code And t2.prodSeriesEnName = t3.prod_series_en_name </select> <select id="getByFactoryHeaderList" resultType="com.huawei.it.tgmes.tmas.basedata.entity.vo.KeyValue"> With weeks As ( With daytimes As ( Select daytime::date From generate_series( (Select #{condition.startweekTimeStr}::date),/*--查询开始日期(可根据需求调整)*/ (Select #{condition.endweekTimeStr}::date),/*--查询结束日期(可根据需求调整)*/ '1 day'<!--间隔(可根据需求调整)--> ) s(daytime) <!--获取连续日期--> ) Select (Select ts.daytime - (Case When extract(Dow From ts.daytime) = 0 Then 7 Else extract(Dow From ts.daytime) End - 1 || 'day')::interval) week1 From daytimes ts Group By week1 Order By week1 Asc <!--获取日期对应的周一--> ) <choose> <when test="condition.dimensionStr eq 1"> select to_char(s.week1,'yyyy-mm-dd') as "key", to_char(s.week1,'yyyy-mm-dd') as "value" from weeks s group by "key" order by "key" asc <!--周--> </when> <when test="condition.dimensionStr eq 2"> select to_char(s.week1,'yyyy-mm') as "key", to_char(s.week1,'yyyy-mm') as "value" from weeks s group by "key" order by "key" asc <!--月--> </when> <when test="condition.dimensionStr eq 3"> select to_char(s.week1,'yyyy')||'_Q'||(select quarters from ( select extract (quarter from s.week1) quarters)t ) as "key" , to_char(s.week1,'yyyy')||'_Q'||(select quarters from ( select extract (quarter from s.week1) quarters)t ) as "value" from weeks s group by "key" order by "key" asc </when> <when test="condition.dimensionStr eq 4"> select to_char(s.week1,'yyyy') as "key",to_char(s.week1,'yyyy') as "value" from weeks s group by "key" order by "key" asc <!--年--> </when> </choose> </select>分析这串 代码
最新发布
08-06
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值