查找返回不同的值，寻找不同的表

查找返回不同的值，寻找不同的表

 

 

select case a_id when 1 then 

(select b_id||' '||b_desc from b where b.b_id=a.a_id)

else 

(select e_id ||' '||e_desc from e where e.e_id=a.a_id)

end from a;

 

=================================================

以上方法的缺陷是单表，判断。今天来了个挑战，更犀利

先看下流程

if(中间，如果第一段sql取到为空的话；)

 

if(){
   select kpi_code
          from dss.dss_ptl_user_customize_kpi f
         where f.login_id = 'CaoBaojun'
           and f.grid_id = 'PTL_VIEW_PRODUCE_D'
   }
  
   else{
           SELECT distinct T.KPI_CODE AS "kpi_code", T.SERVICE_TYPE AS "svc_type"
  FROM dss.DSS_PTL_GRID_KPI T
 WHERE T.GRID_ID = 'PTL_VIEW_PRODUCE_D'
   and t.view_flag = '10'
   AND T.ROLE_ID = 'default'
   }

 

解决方案是 ，把优先级高的，设置为2，然后union all,然后取大的。太犀利了。

 

   with a as (select * from (
select * from
(SELECT distinct T.KPI_CODE AS kpi_code,'1' s
  FROM dss.DSS_PTL_GRID_KPI T
 WHERE T.GRID_ID = 'PTL_VIEW_PRODUCE_D'
   and t.view_flag = '10'
   AND T.ROLE_ID = 'default')
   union all(
   select f.kpi_code,'2' s
          from dss.dss_ptl_user_customize_kpi f
         where f.login_id = 'CaoBaojun'
           and f.grid_id = 'PTL_VIEW_PRODUCE_D')
         ) )
  select kpi_code from a where s= (select max(s) from a)