SQL Server实现数据的递归查询

在一次项目中遇到一种需求，需要记录某产品的替换记录。

实际应用举例为：产品101被201替换，之后201又被303替换，303又被109替换；产品102被202替换，之后202又被105替换。

现在我们需要在已知任何产品序列号时，可以检索出该产品的整个被替换过程和最终的替换结果。

设计表格如下：

产品替换记录（原序列号，替换后序列号）

示例数据如下：

原序列号	替换后序列号
101	201
102	202
201	303
303	109
202	105

 

比如，已经表中存在过201这一产品，那么我想知道201这个产品之后的整个替换过程，那么我们应该可以检索出从201到109的整个过程。

下面我们就来实现这种递归查询：

先创建表并插入示例数据：

CREATE TABLE ProductHistory
 (
    OrgProduct int,
    CurProduct int
);

 INSERT INTO ProductHistory values
    (101,201),
    (102,202),
    (201,303),
    (303,109),
    (202,105)

 

下面开始我们的递归查询：

WITH PHistory(Org,Cur) AS
(
    SELECT OrgProduct,CurProduct FROM ProductHistory WHERE OrgProduct=201
    UNION ALL
    SELECT A.OrgProduct,A.CurProduct FROM ProductHistory A, PHistory B
    WHERE A.OrgProduct=B.Cur
)
select * from PHistory

查询结果如下：

从结果中我们可以看到，201产品被303产品替换了，303产品又被109产品替换了。

如果我们想追溯201产品之前的记录，那么我们只要把查询条件交换一下即可：

WITH PHistory(Org,Cur) AS
(
    SELECT OrgProduct,CurProduct FROM ProductHistory WHERE OrgProduct=201
    UNION ALL
    SELECT A.OrgProduct,A.CurProduct FROM ProductHistory A, PHistory B
    WHERE A.CurProduct=B.Org    --将查询条件交换一下就可以追溯历记录
)
select * from PHistory

结果如下：

可以看出，产品201曾经替换了产品101，而后201产品又被303替换了。

到此，我们成功实现了SQL的递归查询。

遗留问题：暂时我没有想出可以同时向上和向下递归的算法。