如何在MS Access中将多表合并并转置为指定报表格式？

我明白你现在需要把原本按时间行存储的传感器数据，转换成以传感器为行、各采样周期表为列的结构——这本质是个数据转置的需求。下面分你用到的两个环境给出具体实现：

一、MS SQL Server 2017.7 版本实现 #

利用UNPIVOT快速将单表的列转成行，再通过JOIN把10张表的结果按传感器名称合并：

完整SQL代码 #

-- 先定义CTE，获取每张表的最后一行数据
WITH Temp_1min_Last AS (
    SELECT TOP 1 T_01, T_02, T_03, T_04, T_05, T_06
    FROM dbo.Temp_1min
    ORDER BY TheTime DESC
),
Temp_2min_Last AS (
    SELECT TOP 1 T_01, T_02, T_03, T_04, T_05, T_06
    FROM dbo.Temp_2min
    ORDER BY TheTime DESC
),
-- 重复上述CTE，直到Temp_10min_Last
Temp_10min_Last AS (
    SELECT TOP 1 T_01, T_02, T_03, T_04, T_05, T_06
    FROM dbo.Temp_10min
    ORDER BY TheTime DESC
)
-- 转置并合并所有表的数据
SELECT 
    u1.Sensor,
    u1.Temp_1min_Value,
    u2.Temp_2min_Value,
    u3.Temp_3min_Value,
    -- 依次添加到u10.Temp_10min_Value
    u10.Temp_10min_Value
FROM (
    -- 把Temp_1min的列转成行
    SELECT Sensor, Temp_1min_Value
    FROM Temp_1min_Last
    UNPIVOT (
        Temp_1min_Value FOR Sensor IN (T_01, T_02, T_03, T_04, T_05, T_06)
    ) AS unpvt
) u1
LEFT JOIN (
    -- 把Temp_2min的列转成行
    SELECT Sensor, Temp_2min_Value
    FROM Temp_2min_Last
    UNPIVOT (
        Temp_2min_Value FOR Sensor IN (T_01, T_02, T_03, T_04, T_05, T_06)
    ) AS unpvt
) u2 ON u1.Sensor = u2.Sensor
-- 重复LEFT JOIN逻辑，直到Temp_10min
LEFT JOIN (
    SELECT Sensor, Temp_10min_Value
    FROM Temp_10min_Last
    UNPIVOT (
        Temp_10min_Value FOR Sensor IN (T_01, T_02, T_03, T_04, T_05, T_06)
    ) AS unpvt
) u10 ON u1.Sensor = u10.Sensor
ORDER BY u1.Sensor;

二、MS Access 2016 版本实现 #

Access不支持UNPIVOT，我们用UNION ALL手动将列转成行，再通过LEFT JOIN合并：

完整SQL代码 #

SELECT 
    s.Sensor,
    t1.Temp_1min_Value,
    t2.Temp_2min_Value,
    -- 依次添加到t10.Temp_10min_Value
    t10.Temp_10min_Value
FROM (
    -- 生成基础传感器名称列表
    SELECT "T_01" AS Sensor UNION ALL
    SELECT "T_02" AS Sensor UNION ALL
    SELECT "T_03" AS Sensor UNION ALL
    SELECT "T_04" AS Sensor UNION ALL
    SELECT "T_05" AS Sensor UNION ALL
    SELECT "T_06" AS Sensor
) s
LEFT JOIN (
    -- 获取Temp_1min最后一行并转成行
    SELECT "T_01" AS Sensor, T_01 AS Temp_1min_Value
    FROM Temp_1min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_1min)
    UNION ALL
    SELECT "T_02" AS Sensor, T_02 AS Temp_1min_Value
    FROM Temp_1min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_1min)
    UNION ALL
    SELECT "T_03" AS Sensor, T_03 AS Temp_1min_Value
    FROM Temp_1min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_1min)
    UNION ALL
    SELECT "T_04" AS Sensor, T_04 AS Temp_1min_Value
    FROM Temp_1min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_1min)
    UNION ALL
    SELECT "T_05" AS Sensor, T_05 AS Temp_1min_Value
    FROM Temp_1min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_1min)
    UNION ALL
    SELECT "T_06" AS Sensor, T_06 AS Temp_1min_Value
    FROM Temp_1min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_1min)
) t1 ON s.Sensor = t1.Sensor
LEFT JOIN (
    -- 重复上述逻辑处理Temp_2min
    SELECT "T_01" AS Sensor, T_01 AS Temp_2min_Value
    FROM Temp_2min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_2min)
    UNION ALL
    SELECT "T_02" AS Sensor, T_02 AS Temp_2min_Value
    FROM Temp_2min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_2min)
    -- ... 继续添加T_03到T_06的行
) t2 ON s.Sensor = t2.Sensor
-- 重复LEFT JOIN逻辑，直到Temp_10min
LEFT JOIN (
    SELECT "T_01" AS Sensor, T_01 AS Temp_10min_Value
    FROM Temp_10min
    WHERE TheTime = (SELECT MAX(TheTime) FROM Temp_10min)
    UNION ALL
    -- ... 继续添加T_02到T_06的行
) t10 ON s.Sensor = t10.Sensor
ORDER BY s.Sensor;

关键思路说明 #

1. 先拆列成行：把每张表中横向排列的传感器数据，转换成纵向的「传感器名称-对应数值」对，这是实现转置的核心前提。
2. 按传感器关联：以传感器名称为连接键，把10张表的数值列合并到同一行，完美匹配你要的报表结构。
3. 精准取最后一行：通过MAX(TheTime)（Access）或TOP 1 + ORDER BY DESC（SQL Server）确保拿到每张表的最新采样数据。

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