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;