如何编写SQL根据绑定员工ID校验指定条件返回1或0

实现方案 #

你需要先补充两张表的关联条件，再添加EmployeeResidence表的字段校验逻辑即可，另外为了避免不满足条件时返回空结果集而非0的问题，推荐用EXISTS嵌套+CASE判断的写法，逻辑更严谨。

最终SQL代码

SELECT CASE WHEN EXISTS (
    -- 校验Employee表条件
    SELECT 1 
    FROM Employee emp 
    WHERE emp.EmployeeId = :empid 
      AND emp.Job IS NOT NULL
      -- 校验EmployeeResidence表的国籍条件
      AND EXISTS (
          SELECT 1 
          FROM EmployeeResidence er 
          WHERE er.EmployeeId = emp.EmployeeId
            AND er.EmployeeSpclField = 'Nationality'
            AND er.EmployeeSpclValue = 'MEX'
      )
) THEN 1 ELSE 0 END AS check_result
FROM DUAL;

逻辑说明

• 第一层EXISTS先校验传入的:empid在Employee表中存在，且对应的Job字段非空
• 第二层嵌套的EXISTS校验该员工在EmployeeResidence表中，存在EmployeeSpclField为Nationality、且EmployeeSpclValue等于MEX的记录
• 外层用CASE表达式判断两个条件是否同时满足，满足返回1，否则返回0，无论是否符合条件都会返回一行结果，不会出现空结果集的问题

如果你坚持要沿用你最初的多表连接写法，补全后的代码如下（该写法不满足条件时会返回空结果集，不会返回0）：

SELECT 1 
FROM Employee emp, EmployeeResidence er 
WHERE emp.EmployeeId = :empid 
  AND emp.Job IS NOT NULL
  -- 补充两张表的关联条件，避免笛卡尔积
  AND emp.EmployeeId = er.EmployeeId
  -- 补充国籍字段校验逻辑
  AND er.EmployeeSpclField = 'Nationality'
  AND er.EmployeeSpclValue = 'MEX';

内容的提问来源于stack exchange，提问作者david