SQL记录

SQL记录

NVL

NVL(eExpression1, eExpression2)判空,eExpression1为空则取值eExpression2

decode

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

union

union对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序

union all

union all对两个结果集进行并集操作，包括重复行，不进行排序

intersect

intersect对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序

Minus

Minus对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

sum

sum(假如sum统计符合条件的列中全为空，那么返回的是空，这时可以做去空处理NVL(eExpression1,eExpression2))

case when then else end

    select product_id,product_type_id,
    case
    
    when product_type_id=1 then 'Book'
    when product_type_id=2 then 'Video'
    when product_type_id=3 then 'DVD'
    when product_type_id=4 then 'CD'
    else 'Magazine'
    end
    from products 

取同表字段值相同（group by 和having count）

    select *
    from spm.t_spt_material_place n
        where n.material_code in(selectm.material_code
        from spm.t_spt_material_place m
        where m.store_id = 1
        group by m.material_code
        having count(m.material_code) > 1)
    and n.store_id = 1

to_date

    select to_date('2018-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') 
    from dual；

to_char

    select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual;

exists

    select * from V_ALL_WORKFORM_INFO t where t.deleted='Y' and 
    exists (select 1 from v_all_task_info t_i
    where t_i.deleted = 'N' and t_i.workform_id =
    t.workform_id and t_i.unfinishReason ="123" )

wm_concat(多行转一行)

    select t_i.WORKFORM_ID,
    wm_concat('[' || d.text || ']' ||
    t_i.UNFINISHREASON)
    from v_all_task_info t_i
    left join mbes.t_base_data_dictionary d on
    d.key = 'SERVICE_TASK_TYPE'
    and d.value = t_i.TASK_TYPE
    where t_i.DELETED = 'N'
    --and t_i.WORKFORM_ID = '3953483'
    and decode(t_i.source_type, 'SA',
    t_i.WORK_STATUS, t_i.is_finished) = '否'
    group by t_i.WORKFORM_ID;