VB.NET中基于MS Access周期日期的考勤数据查询与补全需求

1. 获取指定周期的起止日期 #

直接通过参数化SQL从PERIOD表提取，避免硬编码：

SELECT STARTDATE, ENDDATE FROM PERIOD WHERE PERIODNAME = ?

在VB.NET中用OleDbCommand绑定参数?为目标周期名称，获取起止日期后作为后续查询的时间范围依据。

2. 合并ABSEN与HELPERABSEN表数据 #

使用UNION ALL合并两张表的原始数据（保留所有记录，不自动去重，效率优于UNION）：

SELECT ID2, DATEABSEN, INOUT, /* 其他需要的业务字段 */ FROM ABSEN
UNION ALL
SELECT ID2, DATEABSEN, INOUT, /* 对应业务字段 */ FROM HELPERABSEN

确保两张表的字段数量、数据类型完全匹配，否则会触发语法错误。

3. 补全周期内缺失的考勤记录 #

核心思路是生成周期内所有应有的记录组合（ID2 + 日期 + INOUT），再与现有数据左连接，补全缺失项。由于不能使用Access内置函数生成日期序列，需依赖一个提前创建的数字辅助表（如NUMBERS，含字段n，存储0~365的整数）。

完整SQL语句（Access 2010+ 支持WITH语法） #

-- 步骤1：合并两张考勤表的数据
WITH CombinedAbsen AS (
    SELECT ID2, DATEABSEN, INOUT, /* 其他业务字段 */ FROM ABSEN
    UNION ALL
    SELECT ID2, DATEABSEN, INOUT, /* 对应业务字段 */ FROM HELPERABSEN
),
-- 步骤2：生成目标周期内的所有日期
PeriodDates AS (
    SELECT DATEADD('d', n, (SELECT STARTDATE FROM PERIOD WHERE PERIODNAME = ?)) AS DATEABSEN
    FROM NUMBERS
    WHERE DATEADD('d', n, (SELECT STARTDATE FROM PERIOD WHERE PERIODNAME = ?)) <= (SELECT ENDDATE FROM PERIOD WHERE PERIODNAME = ?)
),
-- 步骤3：获取所有需要生成记录的ID2和INOUT组合（IN/OUT为固定考勤类型）
EmployeeInOut AS (
    SELECT DISTINCT ID2 FROM CombinedAbsen
    CROSS JOIN (SELECT 'IN' AS INOUT UNION SELECT 'OUT' AS INOUT) AS IO_Types
),
-- 步骤4：生成周期内所有应有的考勤记录框架
ExpectedRecords AS (
    SELECT ei.ID2, pd.DATEABSEN, ei.INOUT
    FROM EmployeeInOut ei
    CROSS JOIN PeriodDates pd
)
-- 步骤5：合并现有记录与补全的缺失记录
SELECT 
    er.ID2, 
    er.DATEABSEN, 
    er.INOUT, 
    ca./* 其他业务字段 */ -- 现有记录的字段，缺失则为NULL
FROM ExpectedRecords er
LEFT JOIN CombinedAbsen ca 
    ON er.ID2 = ca.ID2 
    AND er.DATEABSEN = ca.DATEABSEN 
    AND er.INOUT = ca.INOUT
ORDER BY er.ID2, er.DATEABSEN, er.INOUT

关键说明 #

• 数字辅助表NUMBERS：提前创建并插入0到365的整数，用于生成任意一年以内的日期序列。如果没有该表，也可以用嵌套子查询生成临时数字序列，但效率稍低。
• 参数化查询：所有?占位符在VB.NET中通过OleDbParameter绑定具体值，避免SQL注入风险。
• 兼容旧版Access：如果Access版本不支持WITH语法，可将CTE拆解为嵌套子查询，逻辑保持一致。

VB.NET调用示例 #

Using conn As New OleDbConnection("你的Access连接字符串")
    conn.Open()
    Dim sql As String = ' 上述完整SQL语句
    Using cmd As New OleDbCommand(sql, conn)
        ' Access按参数顺序绑定，此处3个?需重复绑定同一周期名称
        cmd.Parameters.AddWithValue("@PeriodName", "目标周期名称")
        cmd.Parameters.AddWithValue("@PeriodName", "目标周期名称")
        cmd.Parameters.AddWithValue("@PeriodName", "目标周期名称")
        
        ' 读取并处理查询结果
        Using reader As OleDbDataReader = cmd.ExecuteReader()
            ' 填充到DataGridView或实体类集合
        End Using
    End Using
End Using

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