我们经常union一些条件,然后综合判断这个人与没有权限之类的,但是有时候结果集是null的,要么在后台代码里判断,但是后台代码又不让改,必须在sql里改,这里该出几个方法,由简到繁。
-- 0
select nvl(
(select 1 from pm_project where pm_project_id = -1)
, 0) as RESULT_VALUE from dual;
-- 1
select nvl(
(select 1 from pm_project where pm_project_id = 5000041)
, 0) as RESULT_VALUE from dual;
-- 0
select nvl2(
(select 1 from pm_project where pm_project_id = -1)
, 1, 0) as RESULT_VALUE from dual;
-- 1
select nvl2(
(select 1 from pm_project where pm_project_id = 5000041)
, 1, 0) as RESULT_VALUE from dual;
-- 0
select decode(
(select 1 from pm_project where pm_project_id = -1)
, 1, 1, 0) as RESULT_VALUE from dual;
-- 1
select decode(
(select 1 from pm_project where pm_project_id = 5000041)
, 1, 1, 0) as RESULT_VALUE from dual;
-- 0
SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END AS RESULT_VALUE FROM (
select 1 from pm_project where pm_project_id = -1
);
-- 1
SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END AS RESULT_VALUE FROM (
select 1 from pm_project where pm_project_id = 5000041
);