oracle数据库查询语句case的用法

实现功能:

1.先查询status=2的记录,如果查询到记录则返回第一条记录的Product_Name;
2.如果查询不到status=2的记录,则判断status=1的记录是否存在,不存在则返回“请耐心等待!”,存在则返回“拆烟完毕!”

实现思路:

1、使用case语句返回不同的内容:

case语句格式:

case

when 判断语句1 then 返回1

when 判断语句2 then 返回2

……

else 返回n

end

2、解决无记录时需要返回一条null的数据:

select Product_name 

from v_supply_sj 

union select null from dual

3、解决有记录的情况下还会附带一条null数据的问题:

通过判断初始查询结果count(*)是否等于0来决定返回的记录数

最终查询语句:

select case
when t1.Product_name is not null then t1.product_name
when t2.Product_name is not null then '拆烟完毕'
else '请耐心等待' end as ledtext from
(select rownum as q, Product_name from (select Product_name from( select rownum as p,Product_name from v_supply_sj where to_addr = 'SJ03' and bill_type=5 and status = 2 and up_down = '' ) where rownum<=1 union select null from dual )
where rownum<= case
when (select count(*) from (select Product_name from( select rownum as p,Product_name from v_supply_sj where to_addr = 'SJ03' and bill_type=5 and status = 2 and up_down = '' ) where rownum<=1 union select null from dual ))=1
then 1 else
(select count(*) from (select Product_name from( select rownum as p,Product_name from v_supply_sj where to_addr = 'SJ03' and bill_type=5 and status = 2 and up_down = '' ) where rownum<=1 ) ) end ) t1 left join
(select rownum as q, Product_name from (select Product_name from( select rownum as p,Product_name from v_supply_sj where to_addr = 'SJ03' and bill_type=5 and status = 1 and up_down = '' ) where rownum<=1 union select null from dual )
where rownum<= case
when (select count(*) from (select Product_name from( select rownum as p,Product_name from v_supply_sj where to_addr = 'SJ03' and bill_type=5 and status = 1 and up_down = '' ) where rownum<=1 union select null from dual ))=1
then 1 else
(select count(*) from (select Product_name from( select rownum as p,Product_name from v_supply_sj where to_addr = 'SJ03' and bill_type=5 and status = 1 and up_down = '' ) where rownum<=1 ) ) end ) t2 on t1.q=t2.q
上一篇:向架构师进军-->可重用架构资源


下一篇:架构师之路--应用架构的选型和dubbo