为什么向此 SQL Server CTE 表达式添加一行会导致更改

Question

我正在尝试创建一个 CTE（公用表表达式）来创建清单 BOM（物料清单）。 即，查询通过整个 BOM 下降并汇总每个唯一部件的数量。

我还需要返回有关 BOM 项目的一些数据。

我正在尝试添加一个计算字段以显示 BOM 项目是否是装配体，即它是否也有物料清单条目。 我做错了什么？

该数据库是Aras Innovator，它使用SQL Server。

记录结构为

<Part_Id> - <source_id : Bom Entry : related_id> - <Part_Id>

所以基本上有两个表。尽管查询还从其他相关表中返回数据。

第一个查询有效，但是当我尝试添加显示零件是程序集的关系时，总和变为错误，并且返回错误数量的项目。

<!-- language: lang-sql -->
WITH RecursiveBOM(source_id, related_id, quantity) AS
(
  SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
    FROM innovator.PART_BOM AS pbom
    WHERE SOURCE_ID = '82FD83221C5946F0A70CADD328BDB6CA'
  UNION ALL
  SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
    FROM innovator.PART_BOM AS pbom
    INNER JOIN RecursiveBOM rbom ON rbom.RELATED_ID = pbom.SOURCE_ID
)
SELECT
  rp.MAKE_BUY as MAKE_BUY,
  rp.ITEM_NUMBER AS PN,
  rp.NAME AS NAME,
  sum(RecursiveBOM.quantity) as Total,
  mp.ITEM_NUMBER as MPN,
  man.NAME as MANUFACTURER,
  vp.CATALOG_NUMBER as SPN,
  ven.NAME as SUPPLIER,      
  CAST(CASE WHEN count(cp.RELATED_ID)>0 THEN 'Yes' ELSE 'No' END AS TEXT) AS HAS_CAD,
FROM RecursiveBOM
INNER JOIN innovator.PART sp ON RecursiveBOM.source_id = sp.ID
INNER JOIN innovator.PART rp ON RecursiveBOM.related_id = rp.ID
--Manufacturer Part
LEFT JOIN innovator.PART_AML aml ON aml.SOURCE_ID = rp.ID
LEFT JOIN innovator.MANUFACTURER_PART mp ON aml.related_ID = mp.ID
LEFT JOIN innovator.MANUFACTURER man on mp.MANUFACTURER = man.ID
--Supplier
LEFT JOIN innovator.VENDOR_PART vp ON mp.ID = vp.RELATED_ID
LEFT JOIN innovator.VENDOR ven ON vp.SOURCE_ID = ven.ID
--Has drawings
LEFT JOIN innovator.CAD_PART cp ON cp.SOURCE_ID = rp.ID
--Don't decend into PCBA
WHERE sp.classification NOT LIKE '%PCBA%'
--Collect the results 
GROUP BY
  rp.ITEM_NUMBER,
  mp.ITEM_NUMBER,
  rp.NAME,
  man.NAME,
  vp.CATALOG_NUMBER,
  ven.NAME,
  cp.RELATED_ID,
  rp.MAKE_BUY

我只添加了两行，它们是

...
CAST(CASE WHEN count(bom.RELATED_ID)>0 THEN 'Yes' ELSE 'No' END as TEXT) AS IS_ASSY`
...
--Is Assembly
LEFT JOIN innovator.PART_BOM bom ON bom.SOURCE_ID = rp.ID
...

这会产生错误的总和：

<!-- language: lang-sql -->
WITH RecursiveBOM(source_id, related_id, quantity) AS
(
  SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
    FROM innovator.PART_BOM AS pbom
    WHERE SOURCE_ID = '82FD83221C5946F0A70CADD328BDB6CA'
  UNION ALL
  SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
    FROM innovator.PART_BOM AS pbom
    INNER JOIN RecursiveBOM rbom ON rbom.RELATED_ID = pbom.SOURCE_ID
)
SELECT
  rp.MAKE_BUY as MAKE_BUY,
  rp.ITEM_NUMBER AS PN,
  rp.NAME AS NAME,
  sum(RecursiveBOM.quantity) as Total,
  mp.ITEM_NUMBER as MPN,
  man.NAME as MANUFACTURER,
  vp.CATALOG_NUMBER as SPN,
  ven.NAME as SUPPLIER,      
  CAST(CASE WHEN count(cp.RELATED_ID)>0 THEN 'Yes' ELSE 'No' END AS TEXT) AS HAS_CAD,
  CAST(CASE WHEN count(bom.RELATED_ID)>0 THEN 'Yes' ELSE 'No' END as TEXT) AS IS_ASSY
  FROM RecursiveBOM
INNER JOIN innovator.PART sp ON RecursiveBOM.source_id = sp.ID
INNER JOIN innovator.PART rp ON RecursiveBOM.related_id = rp.ID
--Manufacturer Part
LEFT JOIN innovator.PART_AML aml ON aml.SOURCE_ID = rp.ID
LEFT JOIN innovator.MANUFACTURER_PART mp ON aml.related_ID = mp.ID
LEFT JOIN innovator.MANUFACTURER man on mp.MANUFACTURER = man.ID
--Supplier
LEFT JOIN innovator.VENDOR_PART vp ON mp.ID = vp.RELATED_ID
LEFT JOIN innovator.VENDOR ven ON vp.SOURCE_ID = ven.ID
--Has drawings
LEFT JOIN innovator.CAD_PART cp ON cp.SOURCE_ID = rp.ID
--Is Assembly
LEFT JOIN innovator.PART_BOM bom ON bom.SOURCE_ID = rp.ID
--Don't decend into PCBA
WHERE sp.classification NOT LIKE '%PCBA%'
--Collect the results 
GROUP BY
  rp.ITEM_NUMBER,
  mp.ITEM_NUMBER,
  rp.NAME,
  man.NAME,
  vp.CATALOG_NUMBER,
  ven.NAME,
  cp.RELATED_ID,
  rp.MAKE_BUY

Answer 1

有两种可能的替代解决方案应该更干净一些：

1） 使用子选择。 这将导致查询：

...
SELECT
  rp.MAKE_BUY as MAKE_BUY,
  rp.ITEM_NUMBER AS PN,
  rp.NAME AS NAME,
  sum(RecursiveBOM.quantity) as Total,
  mp.ITEM_NUMBER as MPN,
  man.NAME as MANUFACTURER,
  vp.CATALOG_NUMBER as SPN,
  ven.NAME as SUPPLIER,      
  CAST(CASE WHEN count(cp.RELATED_ID)>0 THEN 'Yes' ELSE 'No' END AS TEXT) AS HAS_CAD,
  (select count(*) from innovator.part_bom bom where bom.source_id = rp.ID) AS IS_ASSY
FROM RecursiveBOM
...

2） 链接 CTE 表达式，生成的查询为：

WITH RecursiveBOM(source_id, related_id, quantity) AS
(
  SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
    FROM innovator.PART_BOM AS pbom
    WHERE SOURCE_ID = '82FD83221C5946F0A70CADD328BDB6CA'
  UNION ALL
  SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY
    FROM innovator.PART_BOM AS pbom
    INNER JOIN RecursiveBOM rbom ON rbom.RELATED_ID = pbom.SOURCE_ID
), grouping as (
    SELECT
      rp.MAKE_BUY as MAKE_BUY,
      rp.ITEM_NUMBER AS PN,
      rp.NAME AS NAME,
      sum(RecursiveBOM.quantity) as Total,
      mp.ITEM_NUMBER as MPN,
      man.NAME as MANUFACTURER,
      vp.CATALOG_NUMBER as SPN,
      ven.NAME as SUPPLIER,      
      CAST(CASE WHEN count(cp.RELATED_ID)>0 THEN 'Yes' ELSE 'No' END AS TEXT) AS HAS_CAD,
      rp.id related_id
    FROM RecursiveBOM
INNER JOIN innovator.PART sp ON RecursiveBOM.source_id = sp.ID
INNER JOIN innovator.PART rp ON RecursiveBOM.related_id = rp.ID
--Manufacturer Part
LEFT JOIN innovator.PART_AML aml ON aml.SOURCE_ID = rp.ID
LEFT JOIN innovator.MANUFACTURER_PART mp ON aml.related_ID = mp.ID
LEFT JOIN innovator.MANUFACTURER man on mp.MANUFACTURER = man.ID
--Supplier
LEFT JOIN innovator.VENDOR_PART vp ON mp.ID = vp.RELATED_ID
LEFT JOIN innovator.VENDOR ven ON vp.SOURCE_ID = ven.ID
--Has drawings
LEFT JOIN innovator.CAD_PART cp ON cp.SOURCE_ID = rp.ID
--Don't decend into PCBA
WHERE sp.classification NOT LIKE '%PCBA%'
--Collect the results 
GROUP BY
  rp.ITEM_NUMBER,
  mp.ITEM_NUMBER,
  rp.NAME,
  man.NAME,
  vp.CATALOG_NUMBER,
  ven.NAME,
  cp.RELATED_ID,
  rp.MAKE_BUY,
  rp.id
)
select g.make_buy
  , g.pn
  , g.name
  , g.total
  , g.mpn
  , g.manufacturer
  , g.spn
  , g.supplier
  , g.has_cad
  , CASE WHEN count(bom.id) > 0 then 'Yes' ELSE 'No' END as is_assy
from grouping g
LEFT JOIN innovator.PART_BOM bom ON bom.SOURCE_ID = g.related_id
group by g.make_buy
  , g.pn
  , g.name
  , g.total
  , g.mpn
  , g.manufacturer
  , g.spn
  , g.supplier
  , g.has_cad

Answer 2

我已经找到了一种方法来获得我想要的东西，因此我将其添加为答案。但它看起来并不优雅，还有别的办法吗？

在阅读问题插入到 CTE 中的临时表中时，我意识到我可以通过首先将结果收集到临时表中，然后与 BOM 建立关系来防止行重复。

我相信有更好的方法....

<!-- language: lang-sql -->
create table #TempTable (
  id varchar(32),
  make_buy varchar(100),
  pn varchar(100),
  name varchar(100),
  total int,
  mpn varchar(100),
  manufacturer varchar(100),
  spn varchar(100),
  supplier varchar(100),
  has_cad varchar(100));

WITH RecursiveBOM(source_id, related_id, quantity, prev,next) AS
(
  SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY,0,0
  FROM innovator.PART_BOM AS pbom
  WHERE SOURCE_ID = '82FD83221C5946F0A70CADD328BDB6CA'
  UNION ALL
  SELECT pbom.SOURCE_ID, pbom.RELATED_ID, pbom.QUANTITY, next, prev
  FROM innovator.PART_BOM AS pbom
  INNER JOIN RecursiveBOM rbom ON rbom.RELATED_ID = pbom.SOURCE_ID
)
INSERT INTO #TempTable (id, make_buy, pn, name, total, mpn, manufacturer, spn, supplier, has_cad)
SELECT
  rp.ID,
  rp.MAKE_BUY as MAKE_BUY,
  rp.ITEM_NUMBER AS PN,
  rp.NAME AS NAME,
  sum(RecursiveBOM.quantity) as Total,
  mp.ITEM_NUMBER as MPN,
  man.NAME as MANUFACTURER,
  vp.CATALOG_NUMBER as SPN,
  ven.NAME as SUPPLIER,
  CAST(CASE WHEN count(cp.RELATED_ID)>0 THEN 'Yes' ELSE 'No' END AS TEXT) AS HAS_CAD
FROM RecursiveBOM
INNER JOIN innovator.PART sp ON RecursiveBOM.source_id = sp.ID
INNER JOIN innovator.PART rp ON RecursiveBOM.related_id = rp.ID
--Manufacturer Part
LEFT JOIN innovator.PART_AML aml ON aml.SOURCE_ID = rp.ID
LEFT JOIN innovator.MANUFACTURER_PART mp ON aml.related_ID = mp.ID
LEFT JOIN innovator.MANUFACTURER man on mp.MANUFACTURER = man.ID
--Supplier
LEFT JOIN innovator.VENDOR_PART vp ON mp.ID = vp.RELATED_ID
LEFT JOIN innovator.VENDOR ven ON vp.SOURCE_ID = ven.ID
--Has drawings
LEFT JOIN innovator.CAD_PART cp ON cp.SOURCE_ID = rp.ID
--Don't decend into PCBA
WHERE sp.classification NOT LIKE '%PCBA%'
--Collect the results
GROUP BY
  rp.ID,
  rp.ITEM_NUMBER,
  mp.ITEM_NUMBER,
  rp.NAME,
  man.NAME,
  vp.CATALOG_NUMBER,
  ven.NAME,
  cp.RELATED_ID,
  rp.MAKE_BUY
SELECT
  #TempTable.*,
  CAST(CASE WHEN count(bom.RELATED_ID)>0 THEN 'Yes' ELSE 'No' END as TEXT) AS IS_ASSY
FROM #TempTable
--Is Asseembly
LEFT JOIN innovator.PART_BOM as bom on bom.SOURCE_ID = #TempTable.id
--Collect the results
GROUP BY
  #TempTable.id,
  #TempTable.make_buy,
  #TempTable.pn,
  #TempTable.name,
  #TempTable.total,
  #TempTable.mpn,
  #TempTable.manufacturer,
  #TempTable.spn,
  #TempTable.supplier,
  #TempTable.has_cad
DROP TABLE #TempTable