常用sql汇总

1、判断值

decode( T_YJ_YJSJGLHZB.ZYJSCRQ,' ',' ', to_char((to_date(T_YJ_YJSJGLHZB.ZYJSCRQ,'yyyyMMdd')),'yyyy-MM-dd')) ZYJSCRQ, 

注解：decode用法为 如果T_YJ_YJSJGLHZB.ZYJSCRQ为空格则输出空格，否则输出另外的值

2、关于汇总

select a1.md,sum(a1.statCount) as total,
        sum(case a1.web_id when '1' then a1.statCount else 0 end) as zhiHu,
        sum(case a1.web_id when '2' then a1.statCount else 0 end) as baduZhidao,
        sum(case a1.web_id when '3' then a1.statCount else 0 end) as liuYanBan,
        sum(case a1.web_id when '6' then a1.statCount else 0 end) as wuKong,
        sum(case a1.web_id when '11' then a1.statCount else 0 end) as zhiHuZh
        from (select substr(MAKE_DATE,0,10) md, web_id ,count(1) as statCount
        FROM ask_info t where substr(t.MAKE_DATE,0,10)>to_char(sysdate-3, 'YYYY-MM-DD')
        group by substr(MAKE_DATE,0,10),web_id union
        select substr(MAKE_DATE,0,10) md, decode(web_id,'1','11') ,count(1) as statCount from  askacc_info t where substr(t.MAKE_DATE,0,10)>to_char(sysdate-3, 'YYYY-MM-DD')
        group by substr(MAKE_DATE,0,10),web_id) a1
        group by a1.md order by a1.md desc

注解：sum()函数是汇总函数，when then 与decode()都属于判断函数，substr()属于截取函数，union 是拼接数据函数

3、sign()

decode(sign(T_ZB_CGJHWCLYDHZ.ZYJPCTM-T_ZB_CGJHWCLYDHZ.ZTSYWTM),'1',decode((T_ZB_CGJHWCLYDHZ.ZYJPCTM/(T_ZB_CGJHWCLYDHZ.ZYDHZTM-T_ZB_CGJHWCLYDHZ.ZTSYWTM)*100),'100',
  (to_char((T_ZB_CGJHWCLYDHZ.ZYJPCTM/(T_ZB_CGJHWCLYDHZ.ZYDHZTM-T_ZB_CGJHWCLYDHZ.ZTSYWTM)*100),'FM999,999,999,999,990.000')),
  '100')) || '%' column18,

sign()判断数字值为-1,0,1的函数;

4、Round (expression,numdecimalplaces)

Round (expression,numdecimalplaces)
返回指定位数进行四舍五入的数值
Expression 必选项。数值表达式 被四舍五入。
Numdecimalplaces 
可选项。数字表明小数点右边有多少位进行四舍五入。如果小数位数是负数，则Round（）返回的结果在小数点左端包含指定个零，如果省略，则Round（）返回整数。

5、SUBSTR()

SUBSTR(T_V_YPPWCJDD.MATNR,10) MATNR,

从第10位开始取值

6、递归循环取值

select t.posnr INTO VAR_WBS from t_sap_prhi t
    where t.up='00000000'
    start with t.posnr= V_POS.PS_PSP_PNR
connect by prior t.up=t.posnr;

7、查重排序

select t.zjsqrd,t.zjshh,row_number()over(partition by t.zjsqrd order by t.zjshh)  rn from t_v_htbghzb t

partition by 以zjsqrd分组，以zjshh排序，rn代表行值