在VB.NET中实现MS Access的TRANSFORM、关联查询及考勤字段计算

1. 先构建包含关联与计算字段的基础数据源 #

先通过子查询完成主表与MASTERDAYS的关联，同时计算出DURATIONWORK和LATE字段，避免在透视查询中直接处理复杂关联与计算：

SELECT 
    A.EMP_ID,
    A.RECORD_DATE,
    A.[IN],
    A.[OUT],
    M.DEFAULTIN,
    M.DEFAULTOUT,
    M.DEFAULTREST,
    -- 计算工作时长：OUT - 标准上班时间 - 标准休息时长（Access中时间为双精度类型，可直接算术运算）
    A.[OUT] - M.DEFAULTIN - M.DEFAULTREST AS DURATIONWORK,
    -- 计算迟到标记：IN晚于DEFAULTIN则记为1，否则0（用标准CASE表达式替代Access专属IIF）
    CASE 
        WHEN A.[IN] > M.DEFAULTIN THEN 1 
        ELSE 0 
    END AS LATE
FROM ATTENDANCE A
INNER JOIN MASTERDAYS M 
    ON A.RECORD_DATE = M.DATE -- 按日期关联，可根据实际业务调整关联键

注：ATTENDANCE为假设的主表名称，请替换为你的实际考勤数据表名；若主表与MASTERDAYS是按工作日类型（如周一至周日）关联，需修改ON后的匹配条件。

2. 基于数据源构建透视查询 #

将上述子查询作为数据源，结合TRANSFORM完成透视，同时保留GROUP BY聚合逻辑：

TRANSFORM 
    Sum(DURATIONWORK) AS TOTAL_WORK_DURATION, -- 可根据需求替换为Avg/Count等聚合函数
    Sum(LATE) AS TOTAL_LATE_TIMES
SELECT 
    EMP_ID
FROM (
    -- 嵌入第一步的子查询
    SELECT 
        A.EMP_ID,
        A.RECORD_DATE,
        A.[IN],
        A.[OUT],
        M.DEFAULTIN,
        M.DEFAULTOUT,
        M.DEFAULTREST,
        A.[OUT] - M.DEFAULTIN - M.DEFAULTREST AS DURATIONWORK,
        CASE WHEN A.[IN] > M.DEFAULTIN THEN 1 ELSE 0 END AS LATE
    FROM ATTENDANCE A
    INNER JOIN MASTERDAYS M 
        ON A.RECORD_DATE = M.DATE
) AS DATA_SOURCE
GROUP BY EMP_ID
PIVOT RECORD_DATE; -- 透视维度可替换为部门、月份等，按需调整

3. VB.NET中生成并执行SQL的示例 #

通过OleDbCommand执行上述SQL，若有动态条件建议使用参数化查询：

Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourDatabase.accdb;"
Using conn As New OleDbConnection(connString)
    conn.Open()
    Dim sql As String = "TRANSFORM Sum(DURATIONWORK) AS TOTAL_WORK_DURATION, Sum(LATE) AS TOTAL_LATE_TIMES " &
                        "SELECT EMP_ID " &
                        "FROM ( " &
                        "    SELECT A.EMP_ID, A.RECORD_DATE, A.[IN], A.[OUT], M.DEFAULTIN, M.DEFAULTOUT, M.DEFAULTREST, " &
                        "           A.[OUT] - M.DEFAULTIN - M.DEFAULTREST AS DURATIONWORK, " &
                        "           CASE WHEN A.[IN] > M.DEFAULTIN THEN 1 ELSE 0 END AS LATE " &
                        "    FROM ATTENDANCE A " &
                        "    INNER JOIN MASTERDAYS M ON A.RECORD_DATE = M.DATE " &
                        ") AS DATA_SOURCE " &
                        "GROUP BY EMP_ID " &
                        "PIVOT RECORD_DATE;"
    Using cmd As New OleDbCommand(sql, conn)
        Using reader As OleDbDataReader = cmd.ExecuteReader()
            ' 处理查询结果，示例为填充DataGridView
            Dim dt As New DataTable()
            dt.Load(reader)
            DataGridView1.DataSource = dt
        End Using
    End Using
End Using

关键注意事项 #

• 规避Access内置函数：全程使用标准SQL语法，用CASE替代Access专属的IIF，时间计算直接依赖浮点运算特性，未使用DateDiff等Access专属函数。
• 关联逻辑适配：务必根据实际表结构调整INNER JOIN的关联键，比如若MASTERDAYS按星期定义默认时间，关联条件可改为Weekday(A.RECORD_DATE) = M.DAY_OF_WEEK。
• 透视维度灵活调整：若需按月份透视，可在VB.NET中提前格式化日期为yyyy-MM字符串传入，避免使用Access的Format函数。

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