Oracle如何在sql语句中where子句上加if else判断

像这样
如果 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;

6 个解决方案

#1

case when 代替或者decode

#2

如果 t.id_sup为空，则没有 t.id_sup = ss.supplier_id :
decode(t.id_sup,null,ss.supplier_id,t.id_sup)=ss.supplier_id

如果 t.id_maintenance为空，则没有 t.id_maintenance = sss.supplier_id:
decode(t.id_maintenance,null,sss.supplier_id,t.id_maintenance)=sss.supplier_id

#3

用decode函数：
DECODE (t.id_sup, NULL, ss.supplier_id, t.id_sup) = ss.supplier_id

用case语句
(CASE NVL (t.id_sup, 0) WHEN 0 THEN ss.supplier_id ELSE t.id_sup END) = ss.supplier_id

#4

case when与 decode都可以。
再复杂的用动态SQL语句更好.

#5

涨姿势了~~！

#6

楼主最后是怎么搞定的啊？

#1