请求合并员工出勤统计SQL查询：总工作日与月度工时

数据表结构 #

我有Attendance_Employees和RFID_User两张数据表，表结构如下：

Attendance_Employees表 #

RFID_User表 #

现有查询 #

目前我有两个可正常运行的SQL查询：

1. 统计员工总工作日的查询 #

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '2023-07-01'
SET @EndDate = '2023-07-31'

SELECT [Name],[ID],  COUNT(*) as TotalWorkDays 
FROM
(
  SELECT DISTINCT [Name],[ID],CAST([In_Date] AS DATE) AS [Date]
  FROM [personnel_tracking].[dbo].[Attendance_Employees]
  WHERE [In_Date] IS NOT NULL
  AND CAST([In_Date] AS DATE) BETWEEN @StartDate AND @EndDate 
)T
GROUP BY [Name],[ID] Order by TotalWorkDays desc

该查询返回包含Name、ID、TotalWorkDays的结果集。

2. 统计员工月度工时的查询 #

SELECT B.[Name],B.[ID],SUM(DailyWorkedHours) AS MonthlyWorkedHours
FROM
    (
SELECT base.[Name],base.[ID],base.[Date],base.[In_Time],base.[Out_Time],SUM(base.HoursWorked) AS DailyWorkedHours
        FROM
        (
            SELECT [Name],[ID],[In_Time],[Out_Time],CAST(StartDateTime AS DATE) [Date],
                CASE
                    WHEN DATEDIFF(day, StartDateTime, EndDateTime) = 0 THEN
                        DATEDIFF(minute, StartDateTime, EndDateTime) / 60.0
                    ELSE
                        DATEDIFF(minute, StartDateTime, EndDateTime) / 60.0
                END HoursWorked
            FROM (
                SELECT [Name],[ID],[In_Date],[In_Time],[Out_Date],[Out_Time],CAST([In_Date] AS SMALLDATETIME) + CAST([In_Time] AS SMALLDATETIME) AS StartDateTime,
                    CASE
                        WHEN [In_Time] <= [Out_Time] THEN 
                            CAST([Out_Date] AS SMALLDATETIME) + CAST([Out_Time] AS SMALLDATETIME)
                        ELSE
                            CAST([Out_Date] AS SMALLDATETIME) + CAST([Out_Time] AS SMALLDATETIME)
                    END AS EndDateTime
                FROM
                [personnel_tracking].[dbo].[Attendance_Employees] WHERE [In_Date] BETWEEN @StartDate AND @EndDate
    ) AS firstDay
        ) AS base
        GROUP BY base.[Name],base.[ID],base.[In_Time],base.[Out_Time],base.[Date]  ) AS B  GROUP BY B.[Name],B.[ID] order by MonthlyWorkedHours desc

该查询返回包含Name、ID、MonthlyWorkedHours的结果集。

需求 #

我需要将这两个查询的结果合并为包含Name、ID、TotalWorkDays、MonthlyWorkedHours的统一结果集，示例如下：

请问是否可以使用FULL OUTER JOIN或UNION命令实现这一需求？

解答 #

1. 为什么不适合用UNION #

UNION的作用是纵向合并列结构完全一致的多个结果集，要求列数、数据类型完全匹配，它是把不同结果集的行拼在一起，而不是横向合并列。你的需求是把两个结果集的列放在同一行，所以UNION不符合需求，不能用来实现。

2. 使用FULL OUTER JOIN实现 #

可以用FULL OUTER JOIN基于Name和ID将两个查询的结果合并，这样即使某个员工在其中一个查询中无数据（比如只有工作日记录但无工时记录，反之亦然），也能保留该员工的信息，用ISNULL将NULL值替换为0或0.0，保证结果的完整性。

完整SQL示例：

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '2023-07-01'
SET @EndDate = '2023-07-31'

-- 定义统计工作日的CTE
WITH WorkDaysCTE AS (
    SELECT [Name],[ID],  COUNT(*) as TotalWorkDays 
    FROM
    (
      SELECT DISTINCT [Name],[ID],CAST([In_Date] AS DATE) AS [Date]
      FROM [personnel_tracking].[dbo].[Attendance_Employees]
      WHERE [In_Date] IS NOT NULL
      AND CAST([In_Date] AS DATE) BETWEEN @StartDate AND @EndDate 
    )T
    GROUP BY [Name],[ID]
),
-- 定义统计月度工时的CTE
WorkHoursCTE AS (
    SELECT B.[Name],B.[ID],SUM(DailyWorkedHours) AS MonthlyWorkedHours
    FROM
        (
            SELECT base.[Name],base.[ID],base.[Date],base.[In_Time],base.[Out_Time],SUM(base.HoursWorked) AS DailyWorkedHours
            FROM
            (
                SELECT [Name],[ID],[In_Time],[Out_Time],CAST(StartDateTime AS DATE) [Date],
                    CASE
                        WHEN DATEDIFF(day, StartDateTime, EndDateTime) = 0 THEN
                            DATEDIFF(minute, StartDateTime, EndDateTime) / 60.0
                        ELSE
                            DATEDIFF(minute, StartDateTime, EndDateTime) / 60.0
                    END HoursWorked
                FROM (
                    SELECT [Name],[ID],[In_Date],[In_Time],[Out_Date],[Out_Time],CAST([In_Date] AS SMALLDATETIME) + CAST([In_Time] AS SMALLDATETIME) AS StartDateTime,
                        CASE
                            WHEN [In_Time] <= [Out_Time] THEN 
                                CAST([Out_Date] AS SMALLDATETIME) + CAST([Out_Time] AS SMALLDATETIME)
                            ELSE
                                CAST([Out_Date] AS SMALLDATETIME) + CAST([Out_Time] AS SMALLDATETIME)
                        END AS EndDateTime
                    FROM
                    [personnel_tracking].[dbo].[Attendance_Employees] WHERE [In_Date] BETWEEN @StartDate AND @EndDate
                ) AS firstDay
            ) AS base
            GROUP BY base.[Name],base.[ID],base.[In_Time],base.[Out_Time],base.[Date]  
        ) AS B  
    GROUP BY B.[Name],B.[ID]
)
-- 合并两个CTE的结果
SELECT 
    ISNULL(w.Name, h.Name) AS Name,
    ISNULL(w.ID, h.ID) AS ID,
    ISNULL(w.TotalWorkDays, 0) AS TotalWorkDays,
    ISNULL(h.MonthlyWorkedHours, 0.0) AS MonthlyWorkedHours
FROM WorkDaysCTE w
FULL OUTER JOIN WorkHoursCTE h
    ON w.Name = h.Name AND w.ID = h.ID
ORDER BY TotalWorkDays DESC, MonthlyWorkedHours DESC;

3. 优化建议 #

如果RFID_User是员工主表，建议以它为基础表，用LEFT JOIN关联两个统计结果，这样能确保所有在册员工都出现在结果中，即使没有任何考勤记录：

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '2023-07-01'
SET @EndDate = '2023-07-31'

WITH WorkDaysCTE AS (
    SELECT [Name],[ID],  COUNT(*) as TotalWorkDays 
    FROM
    (
      SELECT DISTINCT [Name],[ID],CAST([In_Date] AS DATE) AS [Date]
      FROM [personnel_tracking].[dbo].[Attendance_Employees]
      WHERE [In_Date] IS NOT NULL
      AND CAST([In_Date] AS DATE) BETWEEN @StartDate AND @EndDate 
    )T
    GROUP BY [Name],[ID]
),
WorkHoursCTE AS (
    SELECT B.[Name],B.[ID],SUM(DailyWorkedHours) AS MonthlyWorkedHours
    FROM
        (
            SELECT base.[Name],base.[ID],base.[Date],base.[In_Time],base.[Out_Time],SUM(base.HoursWorked) AS DailyWorkedHours
            FROM
            (
                SELECT [Name],[ID],[In_Time],[Out_Time],CAST(StartDateTime AS DATE) [Date],
                    CASE
                        WHEN DATEDIFF(day, StartDateTime, EndDateTime) = 0 THEN
                            DATEDIFF(minute, StartDateTime, EndDateTime) / 60.0
                        ELSE
                            DATEDIFF(minute, StartDateTime, EndDateTime) / 60.0
                    END HoursWorked
                FROM (
                    SELECT [Name],[ID],[In_Date],[In_Time],[Out_Date],[Out_Time],CAST([In_Date] AS SMALLDATETIME) + CAST([In_Time] AS SMALLDATETIME) AS StartDateTime,
                        CASE
                            WHEN [In_Time] <= [Out_Time] THEN 
                                CAST([Out_Date] AS SMALLDATETIME) + CAST([Out_Time] AS SMALLDATETIME)
                            ELSE
                                CAST([Out_Date] AS SMALLDATETIME) + CAST([Out_Time] AS SMALLDATETIME)
                        END AS EndDateTime
                    FROM
                    [personnel_tracking].[dbo].[Attendance_Employees] WHERE [In_Date] BETWEEN @StartDate AND @EndDate
                ) AS firstDay
            ) AS base
            GROUP BY base.[Name],base.[ID],base.[In_Time],base.[Out_Time],base.[Date]  
        ) AS B  
    GROUP BY B.[Name],B.[ID]
)
SELECT 
    u.Name,
    u.ID,
    ISNULL(w.TotalWorkDays, 0) AS TotalWorkDays,
    ISNULL(h.MonthlyWorkedHours, 0.0) AS MonthlyWorkedHours
FROM [personnel_tracking].[dbo].[RFID_User] u
LEFT JOIN WorkDaysCTE w ON u.Name = w.Name AND u.ID = w.ID
LEFT JOIN WorkHoursCTE h ON u.Name = h.Name AND u.ID = h.ID
ORDER BY TotalWorkDays DESC, MonthlyWorkedHours DESC;

内容的提问来源于stack exchange，提问作者mustafa dağ