SQL 递归查询的最顶层问题

Question

有个小需求，SQL 递归查询
表样
批次 使用物料批次
A1 B1
B1 C1
C1 D1
A2 B2
B2 C2
C2 D2
需要递归查询出A的所有用料。并且可能输入两组查询条件 用IN 得出结果为
顶层批次 批次 使用物料批次
A1 A1 B1
A1 B1 C1
A1 C1 D1
A2 A2 B2
A2 B2 C2
A2 C2 D2

麻烦各位大侠给出解决方案，小弟不胜感激

Answer 1

假设你的数据在表#a中

WITH t(ppid,pid,id) AS (
     SELECT a.pid AS ppid, * FROM #a AS a WHERE NOT EXISTS (SELECT 0 FROM #a WHERE id=a.pid)
     UNION ALL
     SELECT t.ppid, a.pid,a.id FROM #a AS a INNER JOIN t ON t.id=a.pid  
)
SELECT * FROM t ORDER BY t.ppid

结果：
ppid pid id
A1 A1 B1
A1 B1 C1
A1 C1 D1
A2 A2 B2
A2 B2 C2
A2 C2 D2