SELECT b.* FROM aa a LEFT JOIN bb b ON a.itemcode=b.verid WHERE b.bomusg='z' UNION SELECT b.* FROM aa a LEFT JOIN bb b ON a.itemcode=b.verid WHERE b.bomusg IS NULL
用CTE支持递归查询。下面的语句是对B表的递归,你可以根据你的需要改造一下。 WITH cc AS( SELECT t1.VerID, t1.BOMUsg FROM dbo.bb t1 WHERE t1.BOMUsg = 'z' UNION ALL SELECT t2.VerID, t2.BOMUsg FROM cc AS t1 INNER JOIN dbo.bb AS t2 ON t1.VerID = t2.VerID WHERE t2.BOMUsg = '3' ) SELECT * FROM CC;
WITH cte AS ( SELECT ID,ItemCode,BOMUsage,1 AS leve FROM ( SELECT aa.ID,aa.ItemCode,aa.BOMUsage FROM aa JOIN bb ON aa.ItemCode=bb.VerID AND aa.BOMUsage=bb.BOMUsg )a UNION ALL SELECT b.ID,b.ItemCode,b.BOMUsage,cte.leve+1 AS leve FROM ( SELECT aa.ID,aa.ItemCode,aa.BOMUsage FROM aa JOIN bb ON aa.ItemCode=bb.VerID AND aa.BOMUsage=bb.BOMUsg )b JOIN cte ON b.ID=cte.ItemCode ) SELECT * FROM cte
FROM aa a LEFT JOIN bb b ON a.itemcode=b.verid
WHERE b.bomusg='z'
UNION
SELECT b.*
FROM aa a LEFT JOIN bb b ON a.itemcode=b.verid
WHERE b.bomusg IS NULL
Z10811 Z10051 3 NULL NULL
Z21011 Z15411 3 NULL NULL
Z14511 Z14511 3 NULL NULL
Z21011 Z10811 3 NULL NULL
Z14511 Z13211 3 NULL NULL
这是我要的一部分为3 最后拆到为空就不用拆了 要的就是这些数据 ,还有中间出现bb 表的BOMusg为z的数据
WITH cc AS(
SELECT
t1.VerID, t1.BOMUsg
FROM dbo.bb t1
WHERE t1.BOMUsg = 'z'
UNION ALL
SELECT
t2.VerID, t2.BOMUsg
FROM cc AS t1 INNER JOIN dbo.bb AS t2 ON t1.VerID = t2.VerID
WHERE t2.BOMUsg = '3'
)
SELECT
*
FROM CC;
(
SELECT ID,ItemCode,BOMUsage,1 AS leve FROM
(
SELECT aa.ID,aa.ItemCode,aa.BOMUsage FROM aa
JOIN bb ON aa.ItemCode=bb.VerID AND aa.BOMUsage=bb.BOMUsg
)a
UNION ALL
SELECT b.ID,b.ItemCode,b.BOMUsage,cte.leve+1 AS leve
FROM
(
SELECT aa.ID,aa.ItemCode,aa.BOMUsage FROM aa
JOIN bb ON aa.ItemCode=bb.VerID AND aa.BOMUsage=bb.BOMUsg
)b JOIN cte ON b.ID=cte.ItemCode
)
SELECT * FROM cte