Oracle如何在sql语句中where子句上加if else判断
白然 2013-11-18 06:30:09 像这样
如果 t.id_sup为空,则没有 t.id_sup = ss.supplier_id
如果 t.id_maintenance为空,则没有 t.id_maintenance = sss.supplier_id
and t.id_sup = ss.supplier_id
and t.id_maintenance = sss.supplier_id
select *
from (select t.id,
t.id_kinds,
t.id_code,
'' ci_area,
t.code_goods,
t.name,
t.brand,
t.standard,
t.d_buy,
t.sum,
t.type_buy,
t.type_depreciation,
t.id_rec,
t.id_sup,
t.id_maintenance,
t.d_availability,
t.description,
t.status,
t.id_ne,
t.id_dept,
t.charge_man,
t.accept_date,
t.rfid_lablel,
t.handler,
t.remark,
t.ci_type,
t.d_reject,
to_char(t.d_reject, 'yyyy/mm/dd') new_d_reject,
(ss.name) as su_name,
(sss.name) as ma_name,
tss.staff_name,
n.create_time ci_time,
(tt.name) as ci_type_name
from t_rs_assetcert_info t,
t_ne_type tt,
t_ne n,
t_itsm_supplier ss,
t_itsm_supplier sss,
t_system_staff tss
where t.ci_type = tt.ne_type_code
and t.id_ne = n.ne_id
and t.id_sup = ss.supplier_id
and t.id_maintenance = sss.supplier_id
and t.charge_man = tss.staff_id
and n.ne_id not in
(select ne_id
from t_ne_treeview$ne r, t_ne_treeview v
where r.view_node_code = v.view_node_code
and v.view_node_code like '12%')
union
select t.id,
t.id_kinds,
t.id_code,
v.view_node_code ci_area,
t.code_goods,
t.name,
t.brand,
t.standard,
t.d_buy,
t.sum,
t.type_buy,
t.type_depreciation,
t.id_rec,
t.id_sup,
t.id_maintenance,
t.d_availability,
t.description,
t.status,
t.id_ne,
t.id_dept,
t.charge_man,
t.accept_date,
t.rfid_lablel,
t.handler,
t.remark,
t.ci_type,
t.d_reject,
to_char(t.d_reject, 'yyyy/mm/dd') new_d_reject,
(ss.name) as su_name,
(sss.name) as ma_name,
tss.staff_name,
n.create_time ci_time,
(tt.name) as ci_type_name
from t_rs_assetcert_info t,
t_ne_type tt,
t_ne n,
t_ne_treeview$ne r,
t_ne_treeview v,
t_itsm_supplier ss,
t_itsm_supplier sss,
t_system_staff tss
where t.ci_type = tt.ne_type_code
and t.id_ne = n.ne_id
and n.ne_id = r.ne_id
and t.id_sup = ss.supplier_id
and t.id_maintenance = sss.supplier_id
and t.charge_man = tss.staff_id
and r.view_node_code = v.view_node_code
and v.view_node_code like '12%')
where 1 = 1
order by ci_time desc;