如何编写MS Access SQL查询实现双表工位数量列示输出

咱先明确你的需求：要把Table1（对应Wksp1）和Table2（对应Wksp2）的零件数据合并，将两个车间的数量分别列成单独的列，没有对应数据的填充0，同时要包含两个表中所有的零件记录，最后生成连续的Id序号。

原表结构 #

Table1（Wksp1数据）

+----+--------+--------------+----------+----------+
| Id | PartNo | Nomenclature | Quantity | Wksp     |
+----+--------+--------------+----------+----------+
| 1  | Part1  | Nomenc1      | 2        | Wksp1    |
| 2  | Part2  | Nomenc2      | 4        | Wksp1    |
| 3  | Part3  | Nomenc3      | 6        | Wksp1    |
| 4  | Part4  | Nomenc4      | 8        | Wksp1    |
+----+--------+--------------+----------+----------+

Table2（Wksp2数据）

+----+--------+--------------+----------+-------+
| Id | PartNo | Nomenclature | Quantity | Wksp  |
+----+--------+--------------+----------+-------+
| 1  | Part1  | Nomenc1      | 1        | Wksp2 |
| 2  | Part2  | Nomenc2      | 3        | Wksp2 |
| 3  | Part3  | Nomenc3      | 5        | Wksp2 |
| 4  | Part11 | Nomenc11     | 7        | Wksp2 |
| 5  | Part7  | Nomenc7      | 9        | Wksp2 |
+----+--------+--------------+----------+-------+

期望输出 #

+----+--------+--------------+-------+-------+
| Id | PartNo | Nomenclature | Wksp1 | Wksp2 |
+----+--------+--------------+-------+-------+
| 1  | Part1  | Nomenc1      | 2     | 1     |
| 2  | Part2  | Nomenc2      | 4     | 3     |
| 3  | Part3  | Nomenc3      | 6     | 5     |
| 4  | Part11 | Nomenc11     | 0     | 7     |
| 5  | Part7  | Nomenc7      | 0     | 9     |
| 6  | Part4  | Nomenc4      | 8     | 0     |
+----+--------+--------------+-------+-------+

实现SQL查询 #

因为MS Access不支持直接的FULL OUTER JOIN，咱用UNION + LEFT JOIN的方式模拟全外连接，再用Nz()函数把空值转为0，最后生成连续的Id序号：

SELECT 
    -- 生成连续的自增Id
    (SELECT COUNT(*) FROM 
        (SELECT PartNo, Nomenclature FROM Table1
         UNION
         SELECT PartNo, Nomenclature FROM Table2) AS temp
     WHERE temp.PartNo & temp.Nomenclature <= main.PartNo & main.Nomenclature) AS Id,
    main.PartNo,
    main.Nomenclature,
    -- 左连接Table1，空值转0
    Nz(t1.Quantity, 0) AS Wksp1,
    -- 左连接Table2，空值转0
    Nz(t2.Quantity, 0) AS Wksp2
FROM 
    -- 获取所有唯一的零件组合（PartNo+Nomenclature）
    (SELECT PartNo, Nomenclature FROM Table1
     UNION
     SELECT PartNo, Nomenclature FROM Table2) AS main
LEFT JOIN Table1 AS t1 
    ON main.PartNo = t1.PartNo AND main.Nomenclature = t1.Nomenclature
LEFT JOIN Table2 AS t2 
    ON main.PartNo = t2.PartNo AND main.Nomenclature = t2.Nomenclature
-- 按Id排序，和期望输出顺序一致
ORDER BY Id;

代码解释 #

1. 子查询main：用UNION合并两个表的PartNo和Nomenclature，自动去重，得到所有存在的零件组合。
2. LEFT JOIN：分别关联Table1和Table2，确保即使某个零件只在一个表中存在，也能被保留。
3. Nz()函数：Access特有的函数，用来把空值（NULL）替换为0，完美解决“无对应值填充0”的需求。
4. 自增Id：通过子查询计数生成连续的行号，保证输出的Id是连续的数字。

如果需要调整输出的排序顺序，只需要修改ORDER BY后面的条件即可，比如想先显示Wksp2有数据的记录，可以改成ORDER BY Wksp2 DESC, Wksp1 DESC。

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