SQL来看看，关于父子关系的递归有关问题

SQL高手请进来看看，关于父子关系的递归问题
两张表是1对多的关系，也就主从表关系

table1:

C# code

LotCode   WipID  792      28  793      30  796      33......

table2:

C# code

LotCode WipID  85     28  793    28  88     28  89     28  796    30  85     30  26     33......

现在要求输入一个LotCode，返回他对应的所有LotCode集合

比如说输入了LotCode = 792，则查询对应WipID = 28在table2中有四行数据，其中一行LotNo = 793又包含在table1中，则根据WipID = 30，继续查找到两行记录，其中一行LotNo = 796在table1中存在则继续查找..直到没有为止

就上述几行的例子返回的集合应该是{793,796}


求实现的代码...

[解决办法]

SQL code

CREATE TABLE #T1 (    LotCode INT,    WipID   INT)CREATE TABLE #T2(    LotCode INT,    WipID   INT)INSERT INTO #T1        ( LotCode, WipID )VALUES  ( 792,28),(793,30),(796,33)INSERT INTO #T2        ( LotCode, WipID )VALUES  (85,28),(793,28),(88,28),(89,28),(796,30),(85,30),(26,33);WITH TEMP AS( SELECT  A.LotCode,B.LotCode l2,A.WipID FROM   #T1 A JOIN   #T2 B ON A.WipID = B.WipID WHERE  A.LotCode = 792 UNION ALL SELECT  C.l2 AS LotCode,E.LotCode l2,E.WipID FROM   TEMP C JOIN   #T1 B ON C.l2 = B.LotCode JOIN   #T2 E ON B.WipID = E.WipID)SELECT  LotCodeFROM temp WHERE LotCode <> 792GROUP BY LotCodeDROP TABLE #T1,#T2 

 

查看更多 下一篇