如果列中的值非空，则为MySQL联接条件

Question

查询

SELECT employee.emp_number as employee_id,
employee.termination_id,
employee.firstname as user_name,
termination.termination_date 
FROM employee
JOIN user ON user.emp_number = employee.emp_number 
JOIN reportto ON reportto.emp_number = employee.emp_number 
LEFT JOIN termination ON 
( termination.id = employee.termination_id 
  AND termination.termination_date > '2016-01-01') 
WHERE ohrm_user.created_by = '31' 
GROUP BY employee.emp_number

我在尝试什么

默认情况下，在employee表中，termination_id被设置为NULL。如果employee被终止，则将来自termination表的termination设置为值。我想让所有没有在某一日期之前被终止或终止的员工。

上面的查询得到所有结果，包括termination_date小于2016-01-01的结果.是否可以修改查询，使其省略termination_date小于2016-01-01的结果？或者我必须执行两个不同的查询并组合结果才能实现这一点。

备注:为了避免混乱:我希望那些在2016-01-01之前仍在工作(未被终止)且未被解雇的员工。

Answer 1

试试下面的查询-

SELECT employee.emp_number AS employee_id,
employee.termination_id,
employee.firstname AS user_name,
termination.termination_date 
FROM employee
JOIN USER ON user.emp_number = employee.emp_number 
JOIN reportto ON reportto.emp_number = employee.emp_number 
LEFT JOIN termination ON 
( termination.id = employee.termination_id ) 
WHERE ohrm_user.created_by = '31' 
AND (employee.termination_id IS NULL OR termination.termination_date >= '2016-01-01') 
GROUP BY employee.emp_number

Answer 2

您可以尝试将termination_date上的检查移到WHERE子句：

SELECT employee.emp_number as employee_id, employee.termination_id,
    employee.firstname as user_name, termination.termination_date 
FROM employee
JOIN user
    ON user.emp_number = employee.emp_number 
JOIN reportto
    ON reportto.emp_number = employee.emp_number 
LEFT JOIN termination
    ON termination.id = employee.termination_id 
WHERE (termination.termination_date IS NULL OR termination.termination_date > '2016-01-01')
    AND ohrm_user.created_by = '31'
GROUP BY employee.emp_number

关于WHERE条款的评论：

WHERE (termination.termination_date IS NULL OR termination.termination_date > '2016-01-01')

这将首先检查termination.termination_date是否为NULL，这将指示与termination表不匹配的雇员记录，这意味着他仍在工作。其次，如果termination.termination_date不是NULL，则检查他的终止日期是否比2016-01-01晚。

Answer 3

移动

AND termination.termination_date > '2016-01-01'

从现在的位置到之后

WHERE ohrm_user.created_by = '31'

这应该能起作用