SQL查询用空值显示员工出勤和缺勤情况

Question

我想写一个查询来显示员工一个月的出勤和缺勤情况。我有两张表：Emp和Checkinout

emp table: Emp_ID, Name
Checkinout: ID, Emp_ID_Fk, checkin, checkout

我想要这样的结果：

date, emp.name, checkin, checkout
2019-10-17, John, 2019-10-17, 2019-10-17
2019-10-18, John , null , null <- Absent
2019-10-19, John, 2019-10-19, 2019-10-19
2019-10-17, Mike,2019-10-17, 2019-10-17
2019-10-18, Mike, 2019-10-18,2019-10-18
2019-10-19, Mike , null, null <- absent

这就是我想要的：

with dated as (
select  distinct convert(varchar,checkin,112) Day_Per_Month
from checkinout
where month(checkin) =10)
select convert(date,Day_Per_Month) as Date, emp.name as Employee_Name
from dated 
left join checkinout on convert(varchar,checkin, 112) = Day_Per_Month 
left join emp on emp.emp_id = checkinout.Emp_ID_Fk

注意：当emp不签入时，系统不会在签出表中插入任何记录。

Answer 1

但是您没有指定任何DBMS，所以我尝试使用Sql Server 2012

• 首先，您在CheckInOut表中没有缺席员工的条目，因此您需要为此生成相关日期。因此，通过使用CTE，您可以按照答案中所示的方式执行此操作。它给你从'2019-10-17到'2019-10-20的日期，你可以根据你的要求更改它
• 通过使用CASE和SUBQUERY，您可以筛选出员工在特定日期在场的情况
• 最后，通过使用JOIN(这里我使用旧的联接，因为数据cte和员工之间没有关系(，您可以提取所需的输出

尝试以下查询：

;With Cte_Date AS
(
SELECT CONVERT(DATE,'2019-10-17') AS MonthDate
UNION ALL
SELECT DATEADD(dd,+1,MonthDate) AS MonthDate FROM Cte_Date WHERE MonthDate<=CONVERT(DATE,'2019-10-19')
)
SELECT e.Emp_Id,
e.Name,
d.MonthDate,
CASE WHEN  EXISTS (SELECT 1 FROM CheckInOut AS c WHERE c.CheckIn=d.MonthDate AND c.Emp_Id=e.Emp_Id) THEN d.MonthDate ELSE NULL END AS CheckIn,
CASE WHEN  EXISTS (SELECT 1 FROM CheckInOut AS c WHERE c.CheckOut=d.MonthDate AND c.Emp_Id=e.Emp_Id) THEN d.MonthDate ELSE NULL END AS CheckOut
FROM Cte_Date AS d, Employee AS e
OPTION (MAXRECURSION 0);

演示

注意：语法可能因DBMS而异