精华内容
下载资源
问答
  • 函数篇(上)讲了数字处理、判断、计数&重复、求和,错过了上期了可以点这个传送门:【Excel必知必会】函数篇(上)本篇接着往下讲:05查找匹配5.1 VLOOKUP匹配=VLOOKUP(找什么,查找范围,返回范围中第几列,匹配...
    点击上方蓝色字关注我们!

    函数篇(上)讲了数字处理、判断、计数&重复、求和,错过了上期了可以点这个传送门:【Excel必知必会】函数篇(上)

    本篇接着往下讲:

    05

    查找匹配

    5.1 VLOOKUP匹配

    =VLOOKUP(找什么,查找的范围,返回范围中的第几列,匹配方式)=VLOOKUP(E1,A1:B3,2,0)在A1到B3这个范围内,找E1的值,精确匹配返回A1到B3范围对应的第二列的值

    VLOOKUP应该是大家都非常熟悉的匹配函数了,其实还有很多花里胡哨的用法我觉得没有什么必要去研究,在这里也提示一下,如果是多条件匹配的话,可以先用辅助列将多条件合并为单条件(用"&"将条件连接起来就行了),再进行匹配:

    8a1254a87d68d63c59003770121f6bf3.png

    对于绝大多数人来说VLOOKUP已经能应付工作了,但是他本身还有一些小缺陷,主要体现在两方面,一是只能从左找到右,比如只能在第一列匹配对应第四列的值,不能在第四列匹配对应第一列的值,要达到这个目的的话只能修改数据源的格式,但是很多数据源是不好动;二是要同时匹配行和列的条件,VLOOKUP是做不到的,

    于是我们可以用接下来要讲的INDEX+MATCH组合函数,完美解决上述问题。

    5.2 INDEX+MATCH匹配

    这个函数我拆开讲,但是以后我们在使用的时候是要直接一起写的

    5.2.1 先看INDEX

    =INDEX(查找区域,行数,列数)

    16c274dd7535a5e9f024e07ba7a2e428.png

    =INDEX(B1:B3,2)   返回B1到B3这个区域的第二行的值

    798bb2506d4c190e65c2fed6040896b6.png

    =INDEX(E1:F3,3,2)  返回E1到F3这个区域第三行第二列的值

    这个函数很简单,相信大家看了上述两个例子就懂了,INDEX函数其实就是通过索引号来寻找对应的值,但是索引号得靠我们手动输入,这就达不到我们匹配的目的,总不能我自己去数据源里面找我的目标在第几行吧?

    5.2.2 所以这时就要用到MATCH函数了,它能帮我们找到这个目标值在第几行

    =MATCH(找什么,在哪里找,0)   第三个参数0就是精确匹配

    e39dc94f5917dff14c768d50fdf9dd26.png

    =MATCH(D1,A1:A3,0)   在A1到A3区域找D1的值(湖北),返回湖北在该区域的行号

    好了,到这里我就能用函数,把湖北在B列对应的值匹配出来了,只要我把MATCH函数放在INDEX函数的行号参数里面..

    35f27b5eb7d275dbb3cd7c4e6ca02da2.png

    =INDEX(B1:B3,MATCH(D1,A1:A3,0))   在A1到A3区域中,找D1对应B1到B3的值=INDEX(需要的结果所在区域,MATCH(查找条件,条件所在区域,0)) 这个函数多用几遍很快就能掌握

    这里要注意的一点就是,两个区域的起点必须相同,比如上例中的起点分别是B1和A1,他们是在同一行,如果在A2到A3中找湖北,那么返回的行号是1(因为湖北在这个区域的第一行),行号为1传递到INDEX函数后,它则会返回B1到B3区域的第一行的值,即123,这样匹配到的值全部都是错位,因此两个区域的起点必须相同。

    06

    单元格值的合并、拆分

    6.1 合并多个单元格的值

    =CONCATENATE(A3,A4,A5,A6,A8,A7,E6)=A3&A4&A5&A6...

    上述方法都行,如果用&作为连接符,在单元格中连接特定的字符串的话,用英文符的双引号引起来:

    18b4e5507dbb952a872207d7719f7b56.png

    6.2 计算单元格中的字符数

    = LEN(A1)

    984992088689a947af9b58924d3965f6.png

    返回该单元格的字符数I(标点符号也算一个字符)

    6.3 查找字符在单元格中的位置

    =FIND("查找的内容",单元格,起始位置)   第三个参数起始位置不填的话默认为1,即从左边第一个字开始数

    62663cc159e3237b81c33613b210484f.png

    第三个参数留空,因此返回的是正数第三个的位置。

    6.4 截取字符

    =LEFT(A1,3)    =MID(A1,3,2)=RIGHT(A1,2)

    5d636a8164d2fa144c33160f710daadb.png

    LEFT函数即从左数截取多少长度的字符

    341b89aad58efb2035265f35636ede72.png

    RIGHT函数即从右数截取多少长度的字符

    a7174467e90f98048c878192ee65c118.png

    MID函数即从第二参数的位置开始,截取到第三个参数长度的字符

    结合6.2和6.3的函数,我们又可以构造复合函数来应付稍微复杂的需求了,比如:

    我们平时拿到客户的序时账,经常会是科目代码\科目\明细...这样的结构:

    04ac9cfe7e25df72f7062107b86e7f92.png

    这时我们需要去除前面的科目代码要如何写呢?

    如果科目代码是一个固定的数字的话,比如都是6位数,那么我们用总长度-6,就可以得到除科目代码外的长度,再用RIGHT函数从右边截取这个长度的函数就行了

    c33cbf99819ee8f9ce37e9ade3dc0cf0.png

    当然,这里应该-7,应该还有"\"存在

    但是我们发现前面的科目代码位数是动态的,所以需要用一个函数获取科目代码的位数来代替刚刚的6,这时候就轮到6.3的FIND函数出场,我们从左边开始找"\"是第几位就行了对不对。

    a285836bb9c97da45a2db198078418cd.png

    看,用FIND能返回\在单元格中的位置,因此再把这个函数替换进行即可

    389efb235ad63549219096df8e4b50cb.png

    6.5 清除空格

    =TRIM(A1)    能去除前后的所有空格,若字符中间有空格,则会保留一个空格=SUBSTITUTE(A1," ","")  用空代替所有空格

    这两个函数的作用在右边写了,我们来看看实际的效果吧:

    3c8eee05a8a7681c424082221a83a571.png

    可以看到TRIM函数把前面的空格都去掉了,中间的空格也去掉了3个,还剩一个。

    1a12cbf2a90024592d6fb396f7070d6c.png

    而SUBSTITUTE函数则把所有的函数都替换为了空值。

    就等于我们平时用查找与替换,把空格都替换掉一样,那你可能会问为什么不直接用查找替换还要用公式呢? 

    是因为有时我们的数据源是动态的,但是查找替换的操作得人工一次次来,所以特定情况下用公式会更加方便点。

    07

    日期处理

    关于日期,首先我想告诉大家的是,Excel中的日期,本质上都是数字,看看Office支持中是怎么描述的:

    ce73b100dbf1b5f7fb80fc2413026e80.png

    说人话:你输入的日期,本质都是离1900.1.1距离多少天的数字,如果你把数字2转换为日期,那么它将显示1900.1.2

    7.1 计算两个时间点相差多少天

    如果你理解了我上述的解释的话,要算两个时间点差多少天,就直接用这两个时间相减就行了,因为本质上他就是离1900.1.1有多少天的数字嘛:

    086c44d6a3f93fa9ea3bbac9a6c39fb5.png

    7.2 年、月、日的间隔函数

    =DATEDIF(起始日期,终止日期,信息类型)其中信息类型:"Y":一段时期内的整年数。"M":一段时期内的整月数。"D":一段时期内的天数。"MD":两日期之间天数之差。 忽略日期中的月份和年份。 (Office支持中已不建议只用MD参数,存在错误)"YM":两日期之间月份之差。 忽略日期中的天和年份。"YD":两日期之间的日期部分之差。 忽略日期中的年份。

    一般我们就用Y、D、M就行了

    10a186cb262c3a839a2031e4ca6082e3.png

    这个函数是不能用=DA联想出来的,因为MD参数会出现一定的误差,微软把隐藏了,但是我们只要不要MD参数,还是能直接用的,这里就不展开了,测试固定折旧的时候可以试试。

    end

    好了,函数篇就这里了,一共是7类函数,我认为至少覆盖了工作的90%了,剩下的特殊场景需要用到的函数也可以通过搜索引擎来解决,如果觉得我的文章有用,可以转发到朋友圈哦~

    展开全文
  • 引出了一系列的sql语句第一: 利用to_number的函数转换自动截0 select to_number(to_char(sysdate,'mm‘))||'-'||to_number(to_char(sysdate,'dd’)) from dual;第二: 利用ltrim函数加固定参数去掉0 ...

    今天碰到只要取月份和天数,如果月份前面有0要去掉0.比如说2010-01-08 ,需要的结果是1-8. 引出了一系列的sql语句

    第一: 利用to_number的函数转换自动截0 select to_number(to_char(sysdate,'mm‘))||'-'||to_number(to_char(sysdate,'dd’)) from dual;

    第二: 利用ltrim函数加固定参数去掉0 select ltrim(to_char(sysdate,'mm‘),'0’)||'-'||ltrim(to_char(sysdate,'dd‘),'0’) from dual;

    上面两种方法实现了我们所需要的,共同的特点是都用到两个函数了,但有没有更简单的方法呢 ? 我猜有的人肯定就想到了oracle是不是有这样的格式呢 select to_char(sysdate,'m-d‘) from dual; 很可惜,提示错误,日期格式不存在 .不要灰心,我们继续…

    第三:利用函数的特性。

    我们知道 select sysdate from dual ; 得到的值是:2010-1-8 11:06:18 那有没有函数直接获取到月份1和日8,

    我们的猜测是正确的,确实有这样的函数,出现了下面的函数,

    select extract(month from sysdate) ||'-'|| extract(day from sysdate) month from dual;

    注意: extract 具体的用法:http://www.cnblogs.com/zq281660880/archive/2012/11/09/2762179.html。

    上面的方法有一个共同的特征,就是月和日都是分开取的,怎么样才能 合并到一起去,这样多好啊 !

    看我们的第四种方法:

    第四:巧用oracle自带的格式 select to_char(sysdate,'fmmm-dd‘) from dual;

    ORACLE日期时间函数大全

    TO_DATE格式(以时间:2007-11-02   13:45:25为例)

    Year:

    yy two digits 两位年                显示值:07

    yyy three digits 三位年                显示值:007

    yyyy four digits 四位年                显示值:2007

    Month:

    mm    number     两位月              显示值:11

    mon    abbreviated 字符集表示          显示值:11月,若是英文版,显示nov

    month spelled out 字符集表示          显示值:11月,若是英文版,显示november

    Day:

    dd    number         当月第几天        显示值:02

    ddd    number         当年第几天        显示值:02

    dy    abbreviated 当周第几天简写    显示值:星期五,若是英文版,显示fri

    day    spelled out   当周第几天全写    显示值:星期五,若是英文版,显示friday

    ddspth spelled out, ordinal twelfth

    Hour:

    hh    two digits 12小时进制            显示值:01

    hh24 two digits 24小时进制            显示值:13

    Minute:

    mi    two digits 60进制                显示值:45

    Second:

    ss    two digits 60进制                显示值:25

    其它

    Q     digit         季度                  显示值:4

    WW    digit         当年第几周            显示值:44

    W    digit          当月第几周            显示值:1

    24小时格式下时间范围为: 0:00:00 - 23:59:59....

    12小时格式下时间范围为: 1:00:00 - 12:59:59 ....

    1. 日期和字符转换函数用法(to_date,to_char)

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串

    select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年

    select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月

    select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日

    select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时

    select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分

    select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒

    select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//

    2.

    select to_char( to_date(222,'J'),'Jsp') from dual

    显示Two Hundred Twenty-Two

    3.求某天是星期几

    select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;

    星期一

    select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;

    monday

    设置日期语言

    ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

    也可以这样

    TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')

    4. 两个日期间的天数

    select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

    5. 时间为null的用法

    select id, active_date from table1

    UNION

    select 1, TO_DATE(null) from dual;

    注意要用TO_DATE(null)

    6.月份差

    a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')

    那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。

    所以,当时间需要精确的时候,觉得to_char还是必要的

    7. 日期格式冲突问题

    输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'

    alter system set NLS_DATE_LANGUAGE = American

    alter session set NLS_DATE_LANGUAGE = American

    或者在to_date中写

    select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;

    注意我这只是举了NLS_DATE_LANGUAGE,当然还有很多,

    可查看

    select * from nls_session_parameters

    select * from V$NLS_PARAMETERS

    8.

    select count(*)

    from ( select rownum-1 rnum

    from all_objects

    where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-

    02-01','yyyy-mm-dd')+1

    )

    where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )

    not in ( '1', '7' )

    查找2002-02-28至2002-02-01间除星期一和七的天数

    在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).

    9. 查找月份

    select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;

    1

    select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;

    1.03225806451613

    10. Next_day的用法

    Next_day(date, day)

    Monday-Sunday, for format code DAY

    Mon-Sun, for format code DY

    1-7, for format code D

    11

    select to_char(sysdate,'hh:mi:ss') TIME from all_objects

    注意:第一条记录的TIME 与最后一行是一样的

    可以建立一个函数来处理这个问题

    create or replace function sys_date return date is

    begin

    return sysdate;

    end;

    select to_char(sys_date,'hh:mi:ss') from all_objects;

    12.获得小时数

    extract()找出日期或间隔值的字段值

    SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer

    SQL> select sysdate ,to_char(sysdate,'hh') from dual;

    SYSDATE TO_CHAR(SYSDATE,'HH')

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

    2003-10-13 19:35:21 07

    SQL> select sysdate ,to_char(sysdate,'hh24') from dual;

    SYSDATE TO_CHAR(SYSDATE,'HH24')

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

    2003-10-13 19:35:21 19

    13.年月日的处理

    select older_date,

    newer_date,

    years,

    months,

    abs(

    trunc(

    newer_date-

    add_months( older_date,years*12+months )

    )

    ) days

    from ( select

    trunc(months_between( newer_date, older_date )/12) YEARS,

    mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,

    newer_date,

    older_date

    from (

    select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date

    from emp

    )

    )

    14.处理月份天数不定的办法

    select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual

    16.找出今年的天数

    select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual

    闰年的处理方法

    to_char( last_day( to_date('02'    | | :year,'mmyyyy') ), 'dd' )

    如果是28就不是闰年

    17.yyyy与rrrr的区别

    'YYYY99 TO_C

    ------- ----

    yyyy 99 0099

    rrrr 99 1999

    yyyy 01 0001

    rrrr 01 2001

    18.不同时区的处理

    select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate

    from dual;

    19.5秒钟一个间隔

    Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')

    from dual

    2002-11-1 9:55:00 35786

    SSSSS表示5位秒数

    20.一年的第几天

    select TO_CHAR(SYSDATE,'DDD'),sysdate from dual

    310 2002-11-6 10:03:51

    21.计算小时,分,秒,毫秒

    select

    Days,

    A,

    TRUNC(A*24) Hours,

    TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,

    TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,

    TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds

    from

    (

    select

    trunc(sysdate) Days,

    sysdate - trunc(sysdate) A

    from dual

    )

    select * from tabname

    order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');

    //

    floor((date2-date1) /365) 作为年

    floor((date2-date1, 365) /30) 作为月

    d(mod(date2-date1, 365), 30)作为日.

    23.next_day函数      返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日

    next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。

    1 2 3 4 5 6 7

    日 一 二 三 四 五 六

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

    select    (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual

    日期 返回的是天 然后 转换为ss

    24,round[舍入到最接近的日期](day:舍入到最接近的星期日)

    select sysdate S1,

    round(sysdate) S2 ,

    round(sysdate,'year') YEAR,

    round(sysdate,'month') MONTH ,

    round(sysdate,'day') DAY from dual

    25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型

    select sysdate S1,

    trunc(sysdate) S2,                 //返回当前日期,无时分秒

    trunc(sysdate,'year') YEAR,        //返回当前年的1月1日,无时分秒

    trunc(sysdate,'month') MONTH ,     //返回当前月的1日,无时分秒

    trunc(sysdate,'day') DAY           //返回当前星期的星期天,无时分秒

    from dual

    26,返回日期列表中最晚日期

    select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

    27.计算时间差

    注:oracle时间差是以天数为单位,所以换算成年月,日

    select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual        //时间差-年

    select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual        //时间差-月

    select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual             //时间差-天

    select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual         //时间差-时

    select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual    //时间差-分

    select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒

    28.更新时间

    注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual        //改变时间-年

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual                                 //改变时间-月

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual            //改变时间-日

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual         //改变时间-时

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual      //改变时间-分

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual   //改变时间-秒

    29.查找月的第一天,最后一天

    SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,

    Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,

    Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,

    LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month

    FROM dual;

    三. 字符函数(可用于字面字符或数据库列)

    1,字符串截取

    select substr('abcdef',1,3) from dual

    2,查找子串位置

    select instr('abcfdgfdhd','fd') from dual

    3,字符串连接

    select 'HELLO'||'hello world' from dual;

    4, 1)去掉字符串中的空格

    select ltrim(' abc') s1,

    rtrim('zhang ') s2,

    trim(' zhang ') s3 from dual

    2)去掉前导和后缀

    select trim(leading 9 from 9998767999) s1,

    trim(trailing 9 from 9998767999) s2,

    trim(9 from 9998767999) s3 from dual;

    5,返回字符串首字母的Ascii值

    select ascii('a') from dual

    6,返回ascii值对应的字母

    select chr(97) from dual

    7,计算字符串长度

    select length('abcdef') from dual

    8,initcap(首字母变大写) ,lower(变小写),upper(变大写)

    select lower('ABC') s1,

    upper('def') s2,

    initcap('efg') s3

    from dual;

    9,Replace

    select replace('abc','b','xy') from dual;

    10,translate

    select translate('abc','b','xx') from dual; -- x是1位

    11,lpad [左添充] rpad [右填充](用于控制输出格式)

    select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;

    select lpad(dname,14,'=') from dept;

    12, decode[实现if ..then 逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值

    select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;

    例:

    select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed为111,则取1000;为200,取2000;其它取0

    select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显

    示相等

    13 case[实现switch ..case 逻辑]

    SELECT CASE X-FIELD

    WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'

    WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50'

    WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'

    ELSE 'UNBEKNOWN'

    END

    FROM DUAL

    注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时,用Decode更为简洁。

    四.数字函数

    1,取整函数(ceil 向上取整,floor 向下取整)

    select ceil(66.6) N1,floor(66.6) N2 from dual;

    2, 取幂(power) 和 求平方根(sqrt)

    select power(3,2) N1,sqrt(9) N2 from dual;

    3,求余

    select mod(9,5) from dual;

    4,返回固定小数位数 (round:四舍五入,trunc:直接截断)

    select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

    5,返回值的符号(正数返回为1,负数为-1)

    select sign(-32),sign(293) from dual;

    五.转换函数

    1,to_char()[将日期和数字类型转换成字符类型]

    1) select to_char(sysdate) s1,

    to_char(sysdate,'yyyy-mm-dd') s2,

    to_char(sysdate,'yyyy') s3,

    to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,

    to_char(sysdate, 'hh24:mi:ss') s5,

    to_char(sysdate,'DAY') s6

    from dual;

    2) select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2 from emp

    2, to_date()[将字符类型转换为日期类型]

    insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));

    3, to_number() 转换为数字类型

    select to_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数

    六.其他函数

    1.user:

    返回登录的用户名称

    select user from dual;

    2.vsize:

    返回表达式所需的字节数

    select vsize('HELLO') from dual;

    3.nvl(ex1,ex2):

    ex1值为空则返回ex2,否则返回该值本身ex1(常用)

    例:如果雇员没有佣金,将显示0,否则显示佣金

    select comm,nvl(comm,0) from emp;

    4.nullif(ex1,ex2):

    值相等返空,否则返回第一个值

    例:如果工资和佣金相等,则显示空,否则显示工资

    select nullif(sal,comm),sal,comm from emp;

    5.coalesce:

    返回列表中第一个非空表达式

    select comm,sal,coalesce(comm,sal,sal*10) from emp;

    6.nvl2(ex1,ex2,ex3) :

    如果ex1不为空,显示ex2,否则显示ex3

    如:查看有佣金的雇员姓名以及他们的佣金

    select nvl2(comm,ename,') as HaveCommName,comm from emp;

    七.分组函数

    max min avg count sum

    1,整个结果集是一个组

    1) 求部门30 的最高工资,最低工资,平均工资,总人数,有工作的人数,工种数量及工资总和

    select max(ename),max(sal),

    min(ename),min(sal),

    avg(sal),

    count(*) ,count(job),count(distinct(job)) ,

    sum(sal) from emp where deptno=30;

    2, 带group by 和 having 的分组

    1)按部门分组求最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和

    select deptno, max(ename),max(sal),

    min(ename),min(sal),

    avg(sal),

    count(*) ,count(job),count(distinct(job)) ,

    sum(sal) from emp group by deptno;

    2)部门30的最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和

    select deptno, max(ename),max(sal),

    min(ename),min(sal),

    avg(sal),

    count(*) ,count(job),count(distinct(job)) ,

    sum(sal) from emp group by deptno having deptno=30;

    3, stddev 返回一组值的标准偏差

    select deptno,stddev(sal) from emp group by deptno;

    variance 返回一组值的方差差

    select deptno,variance(sal) from emp group by deptno;

    4, 带有rollup和cube操作符的Group By

    rollup 按分组的第一个列进行统计和最后的小计

    cube 按分组的所有列的进行统计和最后的小计

    select deptno,job ,sum(sal) from emp group by deptno,job;

    select deptno,job ,sum(sal) from emp group by rollup(deptno,job);

    cube 产生组内所有列的统计和最后的小计

    select deptno,job ,sum(sal) from emp group by cube(deptno,job);

    八、临时表

    只在会话期间或在事务处理期间存在的表.

    临时表在插入数据时,动态分配空间

    create global temporary table temp_dept

    (dno number,

    dname varchar2(10))

    on commit delete rows;

    insert into temp_dept values(10,'ABC');

    commit;

    select * from temp_dept; --无数据显示,数据自动清除

    on commit preserve rows:在会话期间表一直可以存在(保留数据)

    on commit delete rows:事务结束清除数据(在事务结束时自动删除表的数据)

    展开全文
  • 去掉前面的0的sql语句(前导零,零前缀)原来sql还有个stuff的函数,很强悍。一个列的格式是单引号后面跟着4位的数字,比如'0003,'0120,'4333,我要转换成3,120,4333这样的格式,就是去掉单引号和前导的0,用以下语句...

    去掉前面的0的sql语句(前导零,零前缀)

    原来sql还有个stuff的函数,很强悍。

    一个列的格式是单引号后面跟着4位的数字,比如'0003,'0120,'4333,我要转换成3,120,4333这样的格式,就是去掉单引号和前导的0,用以下语句就可以。

    SELECT stuff(substring([当前组织],2,4),1,patindex('%[^0]%',substring([当前组织],2,4))-1,''),人员编码

    FROM dbo.orgusermap$

    where 人员编码 is not null

    时间: 2011-08-28

    我的感觉是,Accees数据库虽然可以称得上是小型的关系型数据库,并且也是使用的结构化查询语言SQL,但它的语法(主要体现在函数上),却类似vbscript的语法,我想,这应该和Access属于Office系列有关,基于它的开发和应用,自然就与VBA扯上关系,因而Access的函数库也就是VBA的函数库,而非SQL函数库.下面,我们来具体看下Access和SQL Server在查询语句的编写上具体的不同. 一.数据类型转换: Access: SELECT '调查'+CStr(Did) as di

    SQL语句去掉重复记录,获取重复记录  --查询一个表中有效去掉重复的记录,UserID为自增长主键,RoleID为重复字段 SELECT MIN(UserID) AS UserID, RoleID FROM tmpTable GROUP BY RoleID SELECT RoleID FROM tmpTable GROUP BY RoleID HAVING (COUNT(*) > 1) SELECT DISTINCT * FROM tmpTable sql语句查询 sql server acc

    海量数据(百万以上),其中有些全部字段都相同,有些部分字段相同,怎样高效去除重复? 如果要删除手机(mobilePhone),电话(officePhone),邮件(email)同时都相同的数据,以前一直使用这条语句进行去重: delete from 表 where id not in (select max(id) from 表 group by mobilePhone,officePhone,email ) or delete from 表 where id not in (select mi

    有时我们会碰到类似下面这样的 unicode 字符串: u'\xe4\xbd\xa0\xe5\xa5\xbd' 这明显不是一个正确的 unicode 字符串,可能是在哪个地方转码转错了. 我们要想得到正确的 unicode 字符串首先就必须先将这个字符串转成非 unicode 字符串, 然后再进行解码.按照普通的办法进行 encode 肯定是不行的,因为这不是一个正确的 unicode 字符串: In [1]: u'\xe4\xbd\xa0\xe5\xa5\xbd'.encode('utf8')

    一些小技巧1. 如何查出效率低的语句?在MySQL下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的SQL语句.你也可以在启动配置文件中修改long query的时间,如: 复制代码 代码如下: # Set long query time to 8 seconds    long_query_time=8 2. 如何查询某表的索引?可使用SHOW INDEX语句,如: 复制代码 代码如下

    ffbca9a6dab527cea11e2409d7613d63.gif

    如何在mysql查找效率慢的SQL语句呢?这可能是困然很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL .下面介绍MySQL中如何查询慢的SQL语句 一.MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句 1,slow_query_log 这

    f4c5b4d5b0f2c0003750bbbfee3cb03e.png

    由于最近需要做一些sql query性能提升的研究,因此研究了一下sql语句的解决过程.在园子里看了下,大家写了很多相关的文章,大家的侧重点各有不同.本文是我在看了各种资料后手机总结的,会详细的,一步一步的讲述一个sql语句的各个关键字的解析过程,欢迎大家互相学习. SQL语句的解析顺序 简单的说一个sql语句是按照如下的顺序解析的: 1. FROM FROM后面的表标识了这条语句要查询的数据源.和一些子句如,(1-J1)笛卡尔积,(1-J2)ON过滤,(1-J3)添加外部列,所要应用的对象.F

    下列语句部分是Mssql语句,不可以在access中使用. SQL分类: DDL-数据定义语言(CREATE,ALTER,DROP,DECLARE) DML-数据操纵语言(SELECT,DELETE,UPDATE,INSERT) DCL-数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK) 首先,简要介绍基础语句: 1.说明:创建 数据库 CREATE DATABASE database-name 2.说明:删除数据库 drop database dbname 3.说明:备份

    (高手就不要笑话了^_^). 好了,其他的不说现在就开始: select 子句主要决定了从表中取出的列名,列数以及列的显示顺序等信息,"*"表示查询所有的列,有关select的用法应该结合其它子句的用法. 1.from 子句: ①from子句用于指定被查询的表,试图或快照. ②如果指定多个实体,用逗号讲它们分割.为了查询方便,特别是进行自连接查询时,可以给表起别名.(这里我要说很多刚开始使用sql的朋友开始的时候总觉得这个很简单,没有什么可学的,可是到后来在做一些复杂的sql的时候总是

    展开全文
  • 很多时候我们需要对数字进行格式化,比如位数不足前面加0补足。用PHP自带sprintf函数可以轻松实现。如果想去掉小数后面0可以使用(float)$var。[PHP]//生成4位数,不足前面补0$var=sprintf(“%04d”, 2);echo $...

    很多时候我们需要对数字进行格式化,比如位数不足前面加0补足。用PHP自带的sprintf函数可以轻松实现。

    如果想去掉小数后面的0可以使用(float)$var。

    [PHP]

    //生成4位数,不足前面补0

    $var=sprintf(“%04d”, 2);

    echo $var;//结果为0002

    echo date(‘Y_m_d’, time()).’_’.sprintf(‘d’, rand(0,99));

    ?>

    [/PHP]

    sprintf()函数

    1. 语法

    sprintf(format,arg1,arg2,arg++)

    参数

    描述

    format

    必需。转换格式。

    arg1

    必需。规定插到 format 字符串中第一个 % 符号处的参数。

    arg2

    可选。规定插到 format 字符串中第二个 % 符号处的参数。

    arg++

    可选。规定插到 format 字符串中第三、四等等 % 符号处的参数。

    2. 说明

    参数 format 是转换的格式,以百分比符号 (“%”) 开始到转换字符结束。下面的可能的 format 值:

    %% – 返回百分比符号

    %b – 二进制数

    %c – 依照 ASCII 值的字符

    %d – 带符号十进制数

    %e – 可续计数法(比如 1.5e+3)

    %u – 无符号十进制数

    %f – 浮点数(local settings aware)

    %F – 浮点数(not local settings aware)

    %o – 八进制数

    %s – 字符串

    %x – 十六进制数(小写字母)

    %X – 十六进制数(大写字母)

    arg1, arg2, ++ 等参数将插入到主字符串中的百分号 (%) 符号处。该函数是逐步执行的。在第一个 % 符号中,插入 arg1,在第二个 % 符号处,插入 arg2,依此类推。

    $number = 123;

    $txt = sprintf("%f",$number);

    echo $txt;

    ?>

    3. 格式数字 number_format()

    $number = 1234.56;

    // english notation (default)

    $english_format_number = number_format($number);

    // 1,235

    // French notation

    $nombre_format_francais = number_format($number, 2, ',', ' ');

    // 1 234,56

    $number = 1234.5678;

    // english notation without thousands seperator

    $english_format_number = number_format($number, 2, '.', '');

    // 1234.57

    ?>

    展开全文
  • 原来sql还有个stuff的函数,很强悍。 一个列的格式是单引号后面跟着4位的数字,比如’0003,’0120,’4333,我要转换成3,120,4333这样的格式,就是去掉单引号和前导的0,用以下语句就可以。 SELECT stuff(substring(...
  • Len Len(string|varname) 返回字符串内字符数目,或是存储一...Ltrim Ltrim(string) 将字符串前面的空格去掉  Rtrim Rtrim(string) 将字符串后面空格去掉  Mid Mid(string,start,length) 从string字符串sta
  • function b(id) { console.log("b函数的id:"+id); //var history = "<a href='#' onclick='a(" +id + ")'>历史</a>"; 错误代码这里id默认理解成数字类型,需要"",直接在前面添加会影响ht...
  • php数字转汉字的函数 /*********************数字转汉字***********************/ function del0($num) //去掉数字前面的0 { return "".intval($num); } function n2c($...
  • PHP把阿拉伯数字转换成中文,需要定义一个转换算法: <?... /将数字转换为汉字,...function del0($num) //去掉数字前面的0 { return "".intval($num); } function n2c($x) //单个数字变汉字 { ...
  • php数字转汉字的函数/*********************数字转汉字***********************/function del0($num) //去掉数字前面的0{return "".intval($num);}function n2c($x) //单个数字变汉字{$arr_n = array("零","一",...
  • -- 问题1、、Postgresql中将数字转换为字符串前面多出一个空格。 SELECT TO_CHAR('20181206' + 1, '99999999'); -- 解决1、使用如下,参数二前面加上fm就可以去掉空格了,如下: SELECT TO_CHAR('20181206' + 1, '...
  • -- 问题1、、Postgresql中将数字转换为字符串前面多出一个空格。 SELECT TO_CHAR('20181206' + 1, '99999999'); -- 解决1、使用如下,参数二前面加上fm就可以去掉空格了,如下: SELECT TO_CHAR('20181206' + ...
  • 原来sql还有个stuff的函数,很强悍。一个列的格式是单引号后面跟着4位的数字,比如’0003,’0120,’4333,我要转换成3,120,4333这样的格式,就是去掉单引号和前导的0,用以下语句就可以。SELECT stuff(substring(...
  • en Len(string|varname) 返回字符串内字符数目,...Ltrim Ltrim(string) 将字符串前面的空格去掉 Rtrim Rtrim(string) 将字符串后面空格去掉 Mid Mid(string,start,length) 从string字符串start字符开始取得l
  • 剑指Offer_编程题——调整数组顺序使奇数位于偶数前面题目描述:输入一个整数数组,实现一个函数来调整该数组中数字的顺序,使得所有的奇数位于数组前半部分,所有的偶数位于数组的后半部分,并保证奇数和奇数,偶数...
  • ...2. 去除字符串中连续分割符 4 ...34.去掉字段中多个带区号电话号码前面的区号 65 35.SQL2000/2005字符串拆分为列表通用函数 67 36.求字符串中汉字个数 69 37.根据年月得到当月天数 71 38.全角半角转换函数 71
  • 一个简单的函数,从包含有数字信息的字符串中取出这个数据,利用VAL函数将字符串转换为数值,该函数或略数字字符串后面的字母和汉字信息,所以,只要把数字前面的字母和汉字信息去掉就行了。  '函数,从字符串...
  • 转换函数

    2019-03-27 18:58:50
    1.TO_CHAR:字符串转换函数 查询所有雇员将将年月日分开 ... 10以下前面被被补了前导零,可以使用fm去掉前导零 select empno,name,to_char(hirdate,'fmyyyy-mm-dd')from emp; 2.TO_CHAR还可以给数字...
  • Trim Trim(string) 将字符串前后空格去掉Ltrim Ltrim(string) 将字符串前面的空格去掉Rtrim Rtrim(string) 将字符串后面空格去掉Mid Mid(string,start,length) 从string字符串start字符开始取得l
  • 1:输出给定数字的位数; 2:将给定数字按顺序输出; 3:将给定数字逆序输出。 解决: 1:给定数字除以10,去掉个位,计数器加一,依次直到处于结果为0 2:按顺序输出。 a:计算给定数字位数,目的以及关键点寻找与...
  • EXCEL函数公式集

    热门讨论 2010-03-16 03:26:38
    一个具有11项汇总方式的函数SUBTOTAL 自动排序 按奇偶数排序 自动生成序号 如何自动标示A栏中的数字大小排序? 如何设置自动排序 重复数据得到唯一的排位序列 按字符数量排序 排序字母与数字的混合内容 随机排序 ...
  • atoi是一个可以将字符串转化成数字的函数且遇到非数字字符提前结束,还要处理符号和去掉前面的空格,在直接调用此函数时需要#include <stdlib.h> 在自我实现atoi时主要解决两大问题 1.如何将字符串转换成...
  • asp下格式化时间和日期的函数

    千次阅读 2006-02-04 17:42:00
    我们有时候遇到日期格式可能是2004-1-12 ,系统自动将月份中0去掉了,但是有时候我们需要完整日期...将一个一位的数字前面加零function FillZero(str) ttt=str if len(str)=1 then ttt="0" & str end if 
  • vbscript下格式化时间和日期的函数

    千次阅读 2006-01-20 14:59:00
    我们有时候遇到日期格式可能是2004-1-12 ,系统自动将月份中0去掉了,但是有时候我们需要完整日期...将一个一位的数字前面加零function FillZero(str) ttt=str if len(str)=1 then ttt="0" & str end if F

空空如也

空空如也

1 2 3 4 5 ... 8
收藏数 156
精华内容 62
关键字:

去掉前面数字的函数