时间戳

首页 > 解决方案 > IIF 与 ISNULL？

sql - IIF 与 ISNULL？

问题描述

    SELECT first_name, last_name
    (SELECT  ISNULL(IIF(getdate() between vacation_start and vacation_end, 1, 0),0) from vacatoin_request where mat_emp = W.mat_emp) as is_on_vacation,
    (SELECT  ISNULL(IIF(getdate() between mission_start and mission_end, 1, 0),0) from mission_order where mat_emp = W.mat_emp) as is_on_mission
    FROM
    workers W

IIF 工作正常，但是当我添加 ISNULL 时，如果该工作人员在其他表中没有休假请求或任务订单，它仍然返回 null。

标签： sqlsql-servertsql

解决方案

问题是选择返回 NULL 所以ISNULL需要在选择之外

 SELECT first_name, last_name
    ISNULL((SELECT TOP 1 IIF(getdate() between vacation_start and vacation_end, 1, 0) from vacatoin_request where mat_emp = W.mat_emp),0) as is_on_vacation,
    ISNULL((SELECT TOP 1 IIF(getdate() between mission_start and mission_end, 1, 0) from mission_order where mat_emp = W.mat_emp),0) as is_on_mission
FROM
workers W

推荐阅读