MS Access复杂查询（交叉连接类型）求助：生成有效零件编号组合

问题说明 #

我正在开发一个零件编号系统的变体配置器，现有两个定义编号特性的表，Null代表该组合无效，需要跳过：

• 表1包含PowerSize、Model系列、Input系列、Temp系列字段
• 表2包含Model、Duty系列、Filter系列字段

需要通过查询生成所有有效组合，相关表结构和预期结果示例如下：

表1（Table 1） #

PowerSize | Model A | Model B | Model... | Input A | Input B | Input... | Temp A | Temp B | Temp... |
20HP      | S10     | S30     | null     | 6P      | null    | 5R      | 40C    | 50C    | null    |
200HP     | null    | null    | E10      | 6P      | 3R      | null     | null   | 50C    | 55C    |

表2（Table 2） #

Model | Normal Duty | Heavy Duty | Light Duty | No Filter | Sine Filter |
S10   | N           | null       | null       | N         | S           |
S30   | null        | H          | null       | N         | null        |
E10   | null        | null       | L          | null      | S           |

预期结果示例（Result Example） #

Model | PowerSize | Input | Temp | Duty | Filter |
S10   | 20HP      | 6P    | 40C  | N    | N      |
S10   | 20HP      | 6P    | 40C  | N    | S      |
...（其余示例略）

我试过用DLookup，但没法把Null作为排除条件处理，求可行的实现思路或SQL示例。

实现方案 #

核心思路是把宽表转成窄表（拆分多列的系列字段），过滤掉Null值后再关联生成所有有效组合，完全不需要用DLookup。

1. 拆分表1的系列字段 #

表1是宽表结构，先把Model、Input、Temp的多列拆成单列表，同时过滤Null：

拆分Model系列（保存为查询_拆分Model）

SELECT PowerSize, [Model A] AS Model FROM 表1 WHERE [Model A] IS NOT NULL
UNION ALL
SELECT PowerSize, [Model B] AS Model FROM 表1 WHERE [Model B] IS NOT NULL
UNION ALL
SELECT PowerSize, [Model...] AS Model FROM 表1 WHERE [Model...] IS NOT NULL;

拆分Input系列（保存为查询_拆分Input）

SELECT PowerSize, [Input A] AS Input FROM 表1 WHERE [Input A] IS NOT NULL
UNION ALL
SELECT PowerSize, [Input B] AS Input FROM 表1 WHERE [Input B] IS NOT NULL
UNION ALL
SELECT PowerSize, [Input...] AS Input FROM 表1 WHERE [Input...] IS NOT NULL;

拆分Temp系列（保存为查询_拆分Temp）

SELECT PowerSize, [Temp A] AS Temp FROM 表1 WHERE [Temp A] IS NOT NULL
UNION ALL
SELECT PowerSize, [Temp B] AS Temp FROM 表1 WHERE [Temp B] IS NOT NULL
UNION ALL
SELECT PowerSize, [Temp...] AS Temp FROM 表1 WHERE [Temp...] IS NOT NULL;

2. 拆分表2的系列字段 #

同样处理表2的Duty和Filter列：

拆分Duty系列（保存为查询_拆分Duty）

SELECT Model, [Normal Duty] AS Duty FROM 表2 WHERE [Normal Duty] IS NOT NULL
UNION ALL
SELECT Model, [Heavy Duty] AS Duty FROM 表2 WHERE [Heavy Duty] IS NOT NULL
UNION ALL
SELECT Model, [Light Duty] AS Duty FROM 表2 WHERE [Light Duty] IS NOT NULL;

拆分Filter系列（保存为查询_拆分Filter）

SELECT Model, [No Filter] AS Filter FROM 表2 WHERE [No Filter] IS NOT NULL
UNION ALL
SELECT Model, [Sine Filter] AS Filter FROM 表2 WHERE [Sine Filter] IS NOT NULL;

3. 关联所有拆分后的查询生成最终组合 #

通过PowerSize关联表1的拆分结果，通过Model关联表2的拆分结果，生成所有有效组合：

SELECT 
    m.Model,
    m.PowerSize,
    i.Input,
    t.Temp,
    d.Duty,
    f.Filter
FROM 
    查询_拆分Model m
INNER JOIN 查询_拆分Input i ON m.PowerSize = i.PowerSize
INNER JOIN 查询_拆分Temp t ON m.PowerSize = t.PowerSize
INNER JOIN 查询_拆分Duty d ON m.Model = d.Model
INNER JOIN 查询_拆分Filter f ON m.Model = f.Model;

关键提示 #

• 用UNION ALL拆分时，IS NOT NULL直接过滤无效项，从源头上排除Null
• 最终的多表关联逻辑会自动生成所有符合要求的有效组合，逻辑清晰且效率比DLookup高

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