php - 从今天和最后一个日期的表中获取日期的所有员工

Question

我正在尝试创建一个查询来获取今天和最后一个日期的所有员工。

我有两列 in_date 和 out_date。因此，如果 in_date 和 out_date 列都有记录，我不想获取这些记录。

我只想获取设置了 in_date 但未设置 out_date 的今天和昨天的记录，以及那些甚至没有 in_date 和 out_date 记录的记录。

简单来说，它将从考勤表中获取所有未设置 in_date 和 out_date 的记录。如果两者都设置了，则不获取记录。

这是我到目前为止的查询，不确定我的做法是否正确:

SELECT 
  `E`.`employee_id` AS EmployeeID,
  `E`.`full_name` AS EmployeeNam,
  `ATT`.`in_date` AS EmployeeInDate,
  `ATT`.`out_date` AS EmployeeOutDate,
  `ATT`.`in_time` AS EmployeeInTime,
  `ATT`.`out_time` AS EmployeeOutTime 
FROM
  (`employee` E) 
  INNER JOIN `employment` ET 
    ON `E`.`employee_id` = `ET`.`employee_id` 
    AND ET.current = 1 
  INNER JOIN `employee_project` EP 
    ON `E`.`employee_id` = `EP`.`employee_id` 
  LEFT JOIN `attendence` ATT 
    ON `ATT`.`employee_id` = `E`.`employee_id`  
  INNER JOIN `posting` P 
    ON `P`.`employement_id` = `ET`.`employment_id` 
    AND P.current = 1 
    AND P.status = 2 
WHERE `E`.`enrolled` = 1 -- AND ATT.`in_date` = CURDATE() - 1 OR ATT.`in_date` = CURDATE()
GROUP BY `E`.`employee_id`

这是我现在得到的记录:

我在哪里尝试过这个

AND ATT.`in_date` = CURDATE() - 1 OR ATT.`in_date` = CURDATE()

不确定这是获取今天和昨天日期的正确方法吗？如果不正确，还请告诉我们获取今天和以前的日期的正确方法是什么。

而且它没有给我获取空日期的记录，我如何获取空日期？

空注:我只希望在以下情况下为空:如果员工今天没有出勤，因此它是空的，那么我希望今天的空或最后一天的空(如果存在)(不是所有空记录)。

更新

这次我尝试了UNION方式，但是对于NULL，并集也将两个空值并集？那么如何区分哪个日期为空？

SELECT 
  `E`.`employee_id` AS EmployeeID,
  `E`.`full_name` AS EmployeeNam,
  `ATT`.`in_date` AS EmployeeInDate,
  `ATT`.`out_date` AS EmployeeOutDate,
  `ATT`.`in_time` AS EmployeeInTime,
  `ATT`.`out_time` AS EmployeeOutTime 
FROM
  (`employee` E) 
  INNER JOIN `employment` ET 
    ON `E`.`employee_id` = `ET`.`employee_id` 
    AND ET.current = 1 
  INNER JOIN `employee_project` EP 
    ON `E`.`employee_id` = `EP`.`employee_id` 
  LEFT JOIN `attendence` ATT 
    ON `ATT`.`employee_id` = `E`.`employee_id`  AND ATT.`in_date` = CURDATE() - 1 -- Get Yesterdays Records
  INNER JOIN `posting` P 
    ON `P`.`employement_id` = `ET`.`employment_id` 
    AND P.current = 1 
    AND P.status = 2 
WHERE `E`.`enrolled` = 1
GROUP BY `E`.`employee_id` , ATT.in_date
UNION
SELECT 
  `E`.`employee_id` AS EmployeeID,
  `E`.`full_name` AS EmployeeNam,
  `ATT`.`in_date` AS EmployeeInDate,
  `ATT`.`out_date` AS EmployeeOutDate,
  `ATT`.`in_time` AS EmployeeInTime,
  `ATT`.`out_time` AS EmployeeOutTime 
FROM
  (`employee` E) 
  INNER JOIN `employment` ET 
    ON `E`.`employee_id` = `ET`.`employee_id` 
    AND ET.current = 1 
  INNER JOIN `employee_project` EP 
    ON `E`.`employee_id` = `EP`.`employee_id` 
  LEFT JOIN `attendence` ATT 
    ON `ATT`.`employee_id` = `E`.`employee_id` AND ATT.`in_date` = CURDATE() -- Get Todays Dates
  INNER JOIN `posting` P 
    ON `P`.`employement_id` = `ET`.`employment_id` 
    AND P.current = 1 
    AND P.status = 2 
WHERE `E`.`enrolled` = 1
GROUP BY `E`.`employee_id`

Answer 1

你可以在mysql中尝试类似的东西:

WHERE (ATT.in_date BETWEEN DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) AND CURRENT_TIMESTAMP)

DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) --> 今天日期 - 1 天。

看看这个函数DATE_SUB

希望对您有帮助。