如何查询指定月份所有日期及星期，包含员工缺勤日期

我看了你的代码和需求，问题出在你用了内连接（隐式的逗号连接），这只会返回两边表都有匹配的记录，所以没考勤的日期就被过滤掉了。要实现显示全月所有日期（包括无考勤的），我们需要先生成当月的完整日历，再用左连接关联员工和考勤数据。

修正后的SQL代码 #

DROP TABLE IF EXISTS [Attendance]; 
DROP TABLE IF EXISTS [Employee]; 

CREATE TABLE [Employee] ( 
    [ID] Int NOT NULL PRIMARY KEY, 
    [FirstName] Varchar(25) 
);
INSERT INTO [Employee] VALUES (1, 'Asim');

CREATE TABLE [Attendance] ( 
    ID Int NOT NULL PRIMARY KEY, 
    [Date] Date, 
    Status Varchar(25), 
    [EmpCode] Int, 
    CONSTRAINT FK_EmpCode FOREIGN KEY ([EmpCode]) REFERENCES [Employee](ID) 
);
INSERT INTO [Attendance] VALUES 
(1, '2018-05-02', 'Present', 1),
(2, '2018-05-03', 'Present', 1),
(3, '2018-05-04', 'Present', 1),
(4, '2018-05-07', 'Present', 1),
(5, '2018-05-09', 'Present', 1),
(6, '2018-05-10', 'Present', 1),
(7, '2018-05-11', 'Present', 1),
(8, '2018-05-14', 'Present', 1),
(9, '2018-05-15', 'Present', 1),
(10, '2018-05-16', 'Present', 1);

DECLARE @month AS INT = 5 
DECLARE @Year AS INT = 2018 

-- 生成当月所有日期的CTE
WITH N(N) AS (
    SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1))M(N)
), 
tally(N) AS (
    SELECT ROW_NUMBER() OVER(ORDER BY N.N) FROM N,N a
),
MonthCalendar AS (
    SELECT 
        N AS DAYNUMBER,
        DATEFROMPARTS(@year, @month, N) AS CALENDAR_DATE,
        DATENAME(weekday, DATEFROMPARTS(@year, @month, N)) AS DATEDAY
    FROM tally
    WHERE N <= DAY(EOMONTH(DATEFROMPARTS(@year, @month, 1)))
)
-- 关联员工和考勤表，左连接保留所有日历日期
SELECT 
    mc.DAYNUMBER,
    mc.CALENDAR_DATE AS DATE,
    mc.DATEDAY,
    e.FirstName,
    a.[Date] AS AttendanceDate,
    a.Status, -- 新增显示考勤状态
    DATENAME(month, mc.CALENDAR_DATE) AS 'Month Name'
FROM MonthCalendar mc
CROSS JOIN Employee e -- 每个员工匹配全月日期
LEFT JOIN Attendance a 
    ON a.EmpCode = e.ID 
    AND a.[Date] = mc.CALENDAR_DATE
WHERE e.ID = 1 -- 如果要指定单个员工，加这个条件；要所有员工就去掉
ORDER BY mc.CALENDAR_DATE;

关键修改说明 #

• 生成完整日历表：新增MonthCalendar CTE，专门生成指定月份的所有日期和对应星期，确保不会遗漏任何一天
• 使用左连接：用LEFT JOIN关联考勤表，这样即使没有考勤记录，日历日期也会保留，考勤相关字段显示NULL
• 交叉连接员工表：如果需要显示多个员工的全月考勤，CROSS JOIN会自动为每个员工生成全月的日期行；如果只需要单个员工，加WHERE e.ID = 1过滤即可
• 移除冗余聚合：原代码的GROUP BY是多余的，我们不需要聚合数据，只需要关联展示
• 高效筛选：直接用DATEFROMPARTS匹配日期，比DATENAME更高效，也避免了月份名称的字符串匹配错误

预期结果示例 #

你会看到像5月1日、5日、6日这些无考勤的日期，对应的AttendanceDate和Status字段会显示NULL，比如：

DAYNUMBER DATE       DATEDAY    FirstName AttendanceDate Status  Month Name
--------- ---------- ---------- --------- -------------- ------- ----------
1         2018-05-01 Tuesday    Asim      NULL           NULL    May
2         2018-05-02 Wednesday  Asim      2018-05-02     Present May
3         2018-05-03 Thursday   Asim      2018-05-03     Present May
4         2018-05-04 Friday     Asim      2018-05-04     Present May
5         2018-05-05 Saturday   Asim      NULL           NULL    May
6         2018-05-06 Sunday     Asim      NULL           NULL    May
...

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