sql-server - 具有挑战性的递归 T-SQL 查询

Question

这是我的场景。假设我有两个表“Car”和“CarPart”。汽车由许多部件组成，每个部件可以属于多辆车。在我的例子中，一个复杂的问题是每个零件都会获得一个新的 PartID，即使它是相同的零件名称，但它只是属于不同的汽车。这是我无法控制的事情，所以请耐心等待。这是用于设置的脚本。

IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car
CREATE TABLE Car (
CarID INT,
CarName VARCHAR(16)
)

IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart
CREATE TABLE CarPart (
PartID INT,
PartName VARCHAR(16),
CarID INT
)

INSERT INTO Car
VALUES (1, 'Chevy'),
    (2, 'Ford'),
    (3, 'Toyota'),
    (4, 'Honda'),
    (5, 'Nissan'),
    (6, 'Hugo')

INSERT INTO CarPart 
VALUES  (110, 'Engine', 1),
  (120, 'Engine', 2),
  (210, 'Door', 1),
  (220, 'Door', 3),
  (310, 'Seat', 4),
  (320, 'Seat', 5),
  (410, 'Window', 3),
  (510, 'Wheel', 2),
  (420, 'Window', 6)

如您所见，“Engine”部件同时属于“Chevy”和“Ford”，并且以不同的 ID 列出了两次。再一次，这是我必须忍受的设计限制。

这是我需要完成的任务:给定一辆车，我需要找到这辆车的所有零件以及这些零件所属的所有其他汽车。我必须继续以递归方式查找零件和汽车，直到到达链的末端。逻辑可以概述如下: @StartCar --> @StartCar 的部件 --> 同名的其他部件 --> 获取那些“其他”部件的 Id --> 获取“拥有”这些部件的汽车 - -> 重新开始并重复，直到到达链的末尾。

为了解决我的问题，我尝试了以下查询:

DECLARE @StartCar VARCHAR(16) = 'Chevy'

;WITH cte (CarName, PartName)
AS
(
SELECT c.CarName,
       cp.PartName
FROM CarPart cp
JOIN Car c ON cp.CarID = c.CarID
WHERE c.CarName = @StartCar
UNION ALL
SELECT c.CarName,
       cp.PartName
FROM CarPart cp
JOIN Car c ON cp.CarID = c.CarID
JOIN cte cte ON cp.PartName = cte.PartName
)
SELECT CarName, PartName
FROM cte

但是，它进入无限循环并终止。我希望看到类似这样的输出:

CarName PartName
Chevy Engine
Chevy Door
Ford Engine
Ford Wheel
Toyota Door
Toyota Window
Hugo Window

我感谢任何指点。

谢谢!

Answer 1

您基本上正在遍历一个不是非循环的图，因此您必须明确避免循环。一种方法是跟踪图中的路径。这是应该可以工作的代码。您还可以使用 SQL Server 的 HIERARCHYID 数据类型来保存路径。

我选择将 CTE 制作为汽车表而不是零件表。您的规则永远不会产生特定汽车的某些(但不是全部)零件，因此这看起来更简单。

WITH cte(CarID,hier) AS (
  SELECT CarID, CAST('/'+LTRIM(CarID)+'/' AS varchar(max))
  FROM Car
  WHERE CarName = @StartCar

  UNION ALL

  SELECT c2.CarID, hier+LTRIM(c2.CarID)+'/'
  FROM Car AS c
  JOIN cte ON cte.CarID = c.CarID
  JOIN CarPart AS c1 ON c.CarID = c1.CarID
  JOIN CarPart AS c2 ON c2.PartName = c1.PartName
  WHERE hier NOT LIKE '%/'+LTRIM(c2.CarID)+'/%'
)

SELECT
  c.CarName, cp.PartName
FROM Car AS c
JOIN CarPart AS cp ON cp.CarID = c.CarID
JOIN cte on cte.CarID = c.CarID