如何使一个主表与多个子表复合

Question

我有一个主表(PO_BreakOutAll)，其中~3000行仅由两列(PO_ID、PO_LN_NO)组成，它们共同构成主键。我还有其他几个表，每个表都有一个来自主表的数据子集(至少应该是这样的)。所有表都是与主表相同的架构。

所有表都有一个精确的模式：

PO_ID     char(5) PK
PO_LN_NO  int     PK

我需要做两种不同类型的比较来验证和找到重复的。

首先，要确保主表中的每一行都存在于另一个子表中的一个子表中，并且只存在一个子表中。

其次，我需要确保在任何子表中没有重复行。同一行可以存在于两个或多个子表中，我需要找到它们。

我可以在一个单独的查询中完成每个表，但是还没有弄清楚如何一次编写一个比较所有子表的查询。

以下是我到目前为止取得的成果，但不起作用：

SELECT a.PO_ID as all_PO,
       a.PO_LN_NO,
       c.PO_ID as Cummings_PO,
       c.PO_LN_NO,
       f.PO_ID as filter_PO,
       f.PO_LN_NO,
       fo.PO_ID as fixedObl_PO,
       fo.PO_LN_NO
FROM 
       PO_BreakOutAll   a
       LEFT OUTER JOIN
       PO_Cummins       c   ON (c.PO_ID = a.PO_ID AND c.PO_LN_NO = a.PO_LN_NO)
       LEFT OUTER JOIN
       PO_Filters       f   ON (f.PO_ID = a.PO_ID AND f.PO_LN_NO = a.PO_LN_NO)
       LEFT OUTER JOIN
       PO_FixedOblig    fo  ON (fo.PO_ID = a.PO_ID AND fo.PO_LN_NO = a.PO_LN_NO)

Answer 1

我认为戈登林诺夫有一个全面的解决方案。如果您想使用CTE范例，这里有一个基于Fiddle的示例，它回答了重复的问题：

WITH CTE (PO_ID,PO_LN_NO,TableName) AS

(SELECT 
 PO_ID,
 PO_LN_NO,
 'Cummings' as TableName


 FROM PO_Cummins
 UNION ALL
 SELECT 
 PO_ID,
 PO_LN_NO,
 'Filters' as TableName

 FROM PO_Filters
 UNION ALL
 SELECT 
 PO_ID,
 PO_LN_NO,
 'Office' as TableName

 FROM PO_Office )

SELECT
  PO_BreakOutAll.PO_ID, 
  PO_BreakOutAll.PO_LN_NO,
  CHILD_DATA.TABLENAME AS DUP_TABLENAME
FROM
  PO_BreakOutAll
INNER JOIN (
  SELECT PO_ID, PO_LN_NO, COUNT(1) AS DUP_COUNTER
  FROM CTE
  GROUP BY PO_ID, PO_LN_NO
  HAVING COUNT(1) > 1 
  ) DUPS ON DUPS.PO_ID = PO_BreakOutAll.PO_ID AND DUPS.PO_LN_NO = PO_BreakOutAll.PO_LN_NO
INNER JOIN (
  SELECT PO_ID, PO_LN_NO, TABLENAME
  FROM CTE
  ) CHILD_DATA
  ON CHILD_DATA.PO_ID = PO_BreakOutAll.PO_ID AND CHILD_DATA.PO_LN_NO = PO_BreakOutAll.PO_LN_NO
ORDER BY PO_ID, PO_LN_NO, DUP_TABLENAME

Answer 2

我不会为此使用join；我会使用union all。下面是一种计算表间记录重叠程度的方法：

select isAll, isCummins, isFilters, isOblig, count(*)
from (select PO_ID, PO_LN_NO, sum(isAll) as isAll, sum(isCummins) as isCummins,
             sum(isFilters) as isFilters, sum(isOblig) as isOblig
      from ((select PO_ID, PO_LN_NO, 1 as isAll, 0 as isCummins, 0 as isFilters, 1 as isOblig
             from PO_BreakOutAll
            ) union all
            (select PO_ID, PO_LN_NO, 0, 1, 0, 0
             from PO_Cummins
            ) union all
            (select PO_ID, PO_LN_NO, 0, 0, 1, 0
             from PO_Filters
            ) union all
            (select PO_ID, PO_LN_NO, 0, 0, 0, 1
             from PO_FixedOblig
            )
           ) t
      group by PO_ID, PO_LN_NO
     ) t
group by isAll, isCummins, isFilters, isOblig;

如果要查找测试失败的行，只需使用带有where条件的子查询：

      select PO_ID, PO_LN_NO, sum(isAll) as isAll, sum(isCummins) as isCummins,
             sum(isFilters) as isFilters, sum(isOblig) as isOblig
      from ((select PO_ID, PO_LN_NO, 1 as isAll, 0 as isCummins, 0 as isFilters, 1 as isOblig
             from PO_BreakOutAll
            ) union all
            (select PO_ID, PO_LN_NO, 0, 1, 0, 0
             from PO_Cummins
            ) union all
            (select PO_ID, PO_LN_NO, 0, 0, 1, 0
             from PO_Filters
            ) union all
            (select PO_ID, PO_LN_NO, 0, 0, 0, 1
             from PO_FixedOblig
            )
           ) t
      group by PO_ID, PO_LN_NO
      having sum(isAll) <> 1 or
             (sum(isAll) = 1 and (sum(isCummins) + sum(isFilters) + sum(isOblig) <> 1)
             );