mssql多级查询 CTE递归

来源：互联网发布：怎样建立网络共享编辑：程序博客网时间：2024/05/16 10:26

最近做一个制造业项目中涉及到打包问题，其模式为：一个A->B->C->...可以无限打包，A包含B，B包含C等。

之间遇到了：

1、打包后包裹数量需要更新，更新时必须对他的父级包号也进行更新，所以必须查询出某个包号对应的所有父级包号；

2、拆包（将包裹拆除，希望把A下面的包裹号也连同一起拆除，这样必须查询出某个包号对应的所有子级包号。

后面解决方案：

--建立简单测试打包表CREATE TABLE T_PACK(    ID INT IDENTITY(1,1) PRIMARY KEY,    packNO VARCHAR(40) NOT NULL,    ParentPackNO VARCHAR(40) NULL,PACKQuantity INT)

插入数据方便测试：

INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_001',null,1)INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_002',null,1)INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_003',null,5)INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_004','pack_003',1)INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_005','pack_003',4)INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_006','pack_005',2)INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_007','pack_005',2)INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_009',null, 7)INSERT INTO T_PACK(packNO, ParentPackNO,PACKQuantity) VALUES('pack_010', null,4)

数据表：

通过图中可以看出：pack_007的父级有 pack_005 和pack_003两个

查询所有的父级包号及本身：

WITH CTEGetParent AS  (  SELECT * from T_PACK where packNO=@packNO --传入包号'pack_007'UNION ALL   (SELECT a.* from T_PACK as a inner join    CTEGetParent as b on a.packNO=b.ParentPackNO   )  SELECT * FROM CTEGetParent

查询结果

查询所有的子级包号及本身：

with area as( select * from T_PACK where packNO=@packNO --传入包号'pack_003'union all select a.* from T_PACK a join area b on a.ParentPackNO=b.packNO )select * from area

查询结果：

后面更新数量就通过游标循环ID进行更新。。。。。。

0 0