如何与Case in Where子句进行比较

这个 通用解决方案 -使用布尔逻辑。您不能使用CASE在完整的表达式之间进行选择,所以您应该重写它以使用AND和OR的组合。使用问题中的逻辑,您可以将其改写为:

WHERE p.id = 2
AND 
(
    (p.addid IS NOT NULL AND p.addid = a.id)
    OR (p.addid IS NULL AND 0=0)
)

这最终简化为:

WHERE p.id = 2
AND (p.addid IS NULL OR p.addid = a.id)

这个 查询的特定解决方案 -使用更好的JOIN语法,并简单地利用LEFT JOIN:

SELECT p.name, nvl(a.address,'address not available')
FROM person p
LEFT OUTER JOIN address a ON p.addid = a.id
WHERE p.id = 2

尝试使用 合并 功能如下

select p.name,nvl(a.address,'address not available') from person p,address a
where p.id = 2 
and coalesce(p.addid,a.id)=a.id

select p.name, nvl(a.address, 'address not available') 
from person p left outer join address a 
   on (p.addid = a.id )
where p.id = 2;

你不能使用案例,但你可以通过组合 or s砂 and s

select p.name,
       nvl(a.address,'address not available')
  from person p,
       address a
 where p.id = 2 and 
       ( p.addid is not null AND p.addid = a.id 
         OR 
         p.addid is null
       )        

试试这个:

select p.name,nvl(a.address,'address not available') from person p,address a
where p.id = 2 and a.id = case when p.addid is not null then p.addid else a.id end;