这个只能在程序里面做判断了.或者写个储存过程. 因为判断是否有值只能用 SQL%FOUND来判断了.CREATE PROCEDURE P_EXISTS_VALUE( P_ID VARCHAR2(200)) IS begin select status from barcode where id in P_ID; IF SQL%NOTFOUND THEN select 'None' from dual; end;
--要是IN后面参数不是太多直接用DECODE函数转一下就行了 select decode(status,1,status,2,status,3,status,4,status,5,status,6,status,7,status,'NONE') from barcode;
select (case when ID IS not null then 'status' else null end) status from barcode where id in (1,2,3,4,5,6,7)
select COALESCE(status,'None') from barcode where id in (1,2,3,4,5,6,7); 要注意一点,就是status和none的数据类型
这个sql不治本吧,barcode表中若一条记录id为8,嵌套查询select status from barcode where id in (1, 2, 3, 4, 5, 6, 7) 这个也没有记录返回,左连接和nvl判断完全不起作用、select nvl(status,non ) from ( select status from barcode where id in (1,2,3,4,5,6,7))a right join (select 'NONE' as non FROM DUAL)b on 1=1
这个sql不治本吧,barcode表中若一条记录id为8,嵌套查询select status from barcode where id in (1, 2, 3, 4, 5, 6, 7) 这个也没有记录返回,左连接和nvl判断完全不起作用、select nvl(status,non ) from ( select status from barcode where id in (1,2,3,4,5,6,7))a right join (select 'NONE' as non FROM DUAL)b on 1=1 right join ,that's good.
select nvl(a.name, b.Non) from (select * from t_mytest where id in (1, 2, 3, 4, 5, 6, 7)) a right join (select 'None' as Non from dual) b on 1 = 1;
这么简单而强大的case when 居然没人会用,悲剧 decode只是case的简单版,用在这里就不大灵活了
IS
begin
select status from barcode where id in P_ID;
IF SQL%NOTFOUND THEN
select 'None' from dual;
end;
select decode(status,1,status,2,status,3,status,4,status,5,status,6,status,7,status,'NONE') from barcode;
select nvl(status,non ) from (
select status from barcode where id in (1,2,3,4,5,6,7))a
left join (select 'NONE' as non FROM DUAL)b on 1=1
else null end) status
from barcode where id in (1,2,3,4,5,6,7)
要注意一点,就是status和none的数据类型
select status from barcode where id in (1,2,3,4,5,6,7))a
right join (select 'NONE' as non FROM DUAL)b on 1=1
select status from barcode where id in (1,2,3,4,5,6,7))a
right join (select 'NONE' as non FROM DUAL)b on 1=1
right join ,that's good.
from (select * from t_mytest where id in (1, 2, 3, 4, 5, 6, 7)) a
right join (select 'None' as Non from dual) b on 1 = 1;
decode只是case的简单版,用在这里就不大灵活了