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 = '1'  ) 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 = '1'  ) 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 = '1'  ) 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 = '1'  ) 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 = '1'  ) 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 = '1'  ) where rownum<=1  ) ) end )   t2 on t1.q=t2.q