Microsoft Access多表查询求助：费用记录重复未按预期显示空值

核心原因 #

直接将Customers与Income、Expenses做内连接或简单左连接时，因单个客户存在多条收入记录，费用记录会被每条收入记录关联一次，导致重复显示。要实现每条记录对应单一收入/费用（或空值），需先统一记录维度，再分别关联。

解决方案一：用联合查询合并收入与费用记录 #

先统一Income和Expenses的输出结构，再与Customers关联，确保每条收入/费用为独立记录，无重复：

SELECT 
    c.CustomerID,
    c.CustomerName,
    i.IncomeAmount AS Amount,
    NULL AS ExpenseAmount,
    i.IncomeDate AS RecordDate,
    '收入' AS RecordType
FROM Customers c
LEFT JOIN Income i ON c.CustomerID = i.CustomerID

UNION ALL

SELECT 
    c.CustomerID,
    c.CustomerName,
    NULL AS Amount,
    e.ExpenseAmount,
    e.ExpenseDate AS RecordDate,
    '费用' AS RecordType
FROM Customers c
LEFT JOIN Expenses e ON c.CustomerID = e.CustomerID

ORDER BY c.CustomerID, RecordDate;

说明 #

• UNION ALL保留所有原始记录（无额外去重，效率更高），若需去重可替换为UNION。
• 统一字段RecordDate（记录时间）和RecordType（记录类型），便于分类查看。
• 不存在的字段用NULL填充，符合空值预期。

解决方案二：基于统一时间点关联收入与费用 #

先提取客户所有有收入/费用的日期作为统一记录点，再分别左连两张表，避免交叉重复：

SELECT 
    c.CustomerID,
    c.CustomerName,
    i.IncomeAmount,
    e.ExpenseAmount,
    COALESCE(i.IncomeDate, e.ExpenseDate) AS RecordDate
FROM Customers c
LEFT JOIN (
    SELECT CustomerID, IncomeAmount, IncomeDate FROM Income
    UNION ALL
    SELECT CustomerID, NULL, ExpenseDate FROM Expenses
) AS AllRecords ON c.CustomerID = AllRecords.CustomerID
LEFT JOIN Income i ON c.CustomerID = i.CustomerID AND AllRecords.IncomeDate = i.IncomeDate
LEFT JOIN Expenses e ON c.CustomerID = e.CustomerID AND AllRecords.ExpenseDate = e.ExpenseDate
ORDER BY c.CustomerID, RecordDate;

说明 #

• 通过子查询AllRecords获取客户所有有交易的日期，作为关联基准。
• 分别左连Income和Expenses，确保每个日期点下收入、费用字段独立显示，无重复。

验证要点 #

运行查询后检查：

• 2条收入记录的ExpenseAmount字段应为NULL
• 1条费用记录的IncomeAmount字段应为NULL
• 无重复的费用记录出现

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