PAYATHON 2026

验证联结表数据完整性:SQL Server 基于集合的方法

数据小陈

有。做法是先从权威源 Excel2 生成一份“预期映射”,再拿它和 ProductsSuppliers 里的实际映射做集合比对;直接检查 ProductsSuppliers 本身,查不出业务配对是否正确。如果手里只有 ProductsSuppliers 和主表,能验证的只有外键是否存在、是否重复,无法证明某条 ProductId/SupplierId 配对在业务上成立。要验证业务正确性,必须保留可对照的源数据,例如 Excel2 暂存表。

校验思路

  1. 把 Excel2 原样导入暂存表 dbo.Excel2_Staging,导入时不要手工改数据。
  2. 确保 Products.EnglishName 和 Suppliers.EnglishName 能唯一匹配 Id。有重名先处理,否则按名称 join 会把结果放大。
  3. 用暂存表 join Products、Suppliers,生成 Expected(ProductId, SupplierId)。
  4. 用 FULL OUTER JOIN 或 EXCEPT 对比 Expected 和 ProductsSuppliers,找出缺失的映射和多出来的映射。
  5. 单独检查孤儿外键、重复行、NULL,以及名称里的空格、大小写和排序规则差异。

SQL Server 示例

先检查名称能否唯一映射:

SELECT EnglishName, COUNT(*) AS cnt
FROM dbo.Products
GROUP BY EnglishName
HAVING COUNT(*) > 1;

SELECT EnglishName, COUNT(*) AS cnt
FROM dbo.Suppliers
GROUP BY EnglishName
HAVING COUNT(*) > 1;

检查 Excel2 暂存表中无法匹配主表的行:

SELECT src.Product, src.Supplier,
       CASE WHEN p.Id IS NULL THEN 'Product 未匹配' ELSE '' END AS ProductIssue,
       CASE WHEN s.Id IS NULL THEN 'Supplier 未匹配' ELSE '' END AS SupplierIssue
FROM dbo.Excel2_Staging AS src
LEFT JOIN dbo.Products AS p
       ON p.EnglishName = LTRIM(RTRIM(src.Product))
LEFT JOIN dbo.Suppliers AS s
       ON s.EnglishName = LTRIM(RTRIM(src.Supplier))
WHERE p.Id IS NULL OR s.Id IS NULL;

对比预期映射与联结表实际映射:

;WITH Expected AS (
    SELECT DISTINCT p.Id AS ProductId, s.Id AS SupplierId
    FROM dbo.Excel2_Staging AS src
    JOIN dbo.Products AS p
      ON p.EnglishName = LTRIM(RTRIM(src.Product))
    JOIN dbo.Suppliers AS s
      ON s.EnglishName = LTRIM(RTRIM(src.Supplier))
),
Actual AS (
    SELECT DISTINCT ProductId, SupplierId
    FROM dbo.ProductsSuppliers
)
SELECT COALESCE(e.ProductId, a.ProductId) AS ProductId,
       COALESCE(e.SupplierId, a.SupplierId) AS SupplierId,
       CASE
           WHEN a.ProductId IS NULL THEN 'MissingInJunction'
           WHEN e.ProductId IS NULL THEN 'ExtraInJunction'
       END AS Issue
FROM Expected AS e
FULL OUTER JOIN Actual AS a
  ON a.ProductId = e.ProductId
 AND a.SupplierId = e.SupplierId
WHERE e.ProductId IS NULL OR a.ProductId IS NULL;

检查孤儿外键和重复映射:

SELECT 'ProductId 无对应 Products' AS Issue, ps.ProductId, ps.SupplierId
FROM dbo.ProductsSuppliers AS ps
LEFT JOIN dbo.Products AS p ON p.Id = ps.ProductId
WHERE p.Id IS NULL

UNION ALL

SELECT 'SupplierId 无对应 Suppliers' AS Issue, ps.ProductId, ps.SupplierId
FROM dbo.ProductsSuppliers AS ps
LEFT JOIN dbo.Suppliers AS s ON s.Id = ps.SupplierId
WHERE s.Id IS NULL;

SELECT ProductId, SupplierId, COUNT(*) AS cnt
FROM dbo.ProductsSuppliers
GROUP BY ProductId, SupplierId
HAVING COUNT(*) > 1;

其他联结表怎么复用

可以把上面的做法整理成模板:源暂存表 + 名称到 Id 的解析查询 + Expected CTE + FULL OUTER JOIN。每张联结表只需要替换目标表名和两个业务键列。如果只想批量检查所有联结表的外键完整性,可以从 sys.foreign_keys、sys.foreign_key_columns 生成动态 SQL,统一检查孤儿记录;业务配对是否正确,仍然要用各自的源数据来对照。

注意事项

  • Excel 导入后要留意空格、大小写、数字被识别成文本、排序规则不一致。建议在比对前统一 LTRIM/RTRIM,并确认 collation。
  • 用 FULL OUTER JOIN 比对时,对 Expected 和 Actual 使用 DISTINCT,避免重复行干扰映射差异;重复行用单独查询检查。
  • 如果 ProductsSuppliers 在业务上不允许重复,建议加 PRIMARY KEY(ProductId, SupplierId) 或唯一约束。
  • C# 插入逻辑也应有事务、异常处理和参数化;验证阶段以数据库中的集合查询为准。

备注:内容仅供参考。