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

Question

我正在尝试创建CTE (公用表表达式)来创建清单BOM (物料清单)。即，查询向下遍历整个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

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

在阅读问题Insert into a Temp Table in a CTE时，我意识到我可以通过首先将结果收集到一个临时表中，然后建立与物料清单的关系来防止行重复。

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

<!-- 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