sql - 在 SQL Server 中创建版本化数据并查找版本之间的差异

Question

我正在寻找一个好的数据库设计解决方案，以便轻松比较我的数据版本信息。例如，请查看下面我的场景(尽管我无法将实际场景放入我们的应用程序中 - 对此深表歉意。)

示例数据:

Assembly
-----------
AssemblyId       AssemblyId
--------------------------
1        Assembly1
2        Assembly2


BOM
--------------------------
BOMId   AssemblyId       VersionName        ParentBOMId
-----------------------------------------------------
1           1           V001               NULL
2           1           V002               1
3           1           V003               2
4           1           V004               3
5           1           V005               2

[每次更改 BOM 记录时，它都继承自任何先前版本 (ParentBOMID) - 如果它是装配体的第一个版本，则 ParentBOMID 为 Null]

BOMDetail
-----------
ID     BOMID       PartName       Qty  
---------------------------------------------------
1     1          Part1             2
2     1          Part2             1
3     1          Part3             2

4     2          Part1             2
5     2          Part2             1
6     2          Part4             2  {Change in PartName here in V002 Base Version V001}

7     3          Part1             2
8     3          Part2             1
9     3          Part4             2
10    3          Part5             9 {Added this part into V003 Base Version V002}

11    4          Part1             2
12    4          Part2             1
13    4          Part4             2
14    4          Part6             9 {this part changed into V004 Base Version V003}

15     5          Part1            2
16     5          Part2            1
17     5          Part9            2  {Change in PartName here in V002 Base Version V002}

我需要相互比较不同的 BOM 版本。如果需要比较 V001 与 V002 或 V004 与 V003 很容易，因为 ParentBOMId 关系很容易获得。但就 V005 而言，当它是从 V002 创建时，如果有人想将 V004 与 V001 进行比较，那就有点困难了。

附言我无法为我必须使用的部件创建主表，仅比较部件名称。

在版本控制频率非常高并且父 >> 父 >> 父关系可能在层次结构中非常深的情况下，任何人都可以建议我不同的数据库设计或简单的解决方案。

例如我正在比较 V001 和 V002 输出应该是

V001 第 1 部分 2 V002 第 1 部分 2

V001 第 2 部分 1 V002 第 2 部分 1

V001 第 3 部分 2 V002 空 0

V001 空 0 V002 第 4 部分 2

我应该能够将任何版本相互比较并发现差异，如上所示。

Answer 1

对于类似的事情，我会使用两种基本方法......

SELECT
  *
FROM
  (SELECT * FROM BomDetail WHERE BomID = 1)     AS bom1
FULL OUTER JOIN
  (SELECT * FROM BomDetail WHERE BomID = 2)     AS bom2
    ON bom1.PartName = bom2.PartName

SELECT
  PartName,
  SUM(CASE WHEN BomID = 1 THEN qty ELSE 0 END)   AS bom1qty,
  SUM(CASE WHEN BomID = 2 THEN qty ELSE 0 END)   AS bom2qty
FROM
  BomDetail
WHERE
  BomID IN (1,2)
GROUP BY
  PartName

虽然第二个较长，但它也不需要进行连接，因此在某些情况下可以更快。