sql - 如果值不存在则返回 null

Question

我有一个包含项目数据的表，如果未找到 project_ID，我想返回 NULL 或“Not_Exist”作为结果。

例如:

SELECT a.[Project ID], a.[Stage]
FROM Projects a
WHERE a.[Project ID]  IN ('CR324S', 'a')

CR324S 存在于表中，但 'a' 不存在，所以我想要的结果是:

Project ID     Stage
-----------------------------
CR324S         Implementation
a              Not_Exist

Answer 1

你可以试试下面-

1. 首先使用联合构造一组值
2. 然后使用 left join 并将您的条件放在 ON 子句中

SELECT a.[pid], coalesce(b.[Stage],'Not_Exist') as stage
from
(
    select 'CR324S' as pid 
    union 
    select 'a'
)A left join Projects B on A.pid=B.[Project ID] and [Project ID]  IN ('CR324S','a')

或

SELECT a.[pid], coalesce(b.[Stage],'Not_Exist') AS stage
FROM   (VALUES ('CR324S'), ('a')) A(pid)
LEFT JOIN Projects B on A.pid=B.[Project ID] AND [Project ID]  IN ('CR324S','a')