MSSQL递归查询实例

NOT EXISTS的用法

/*
-- 示例数据
DROP TABLE test_data.dbo.t_releation;
CREATE TABLE test_data.dbo.t_releation(
row_id INT PRIMARY KEY IDENTITY(1,1), -- 行号
parent_id VARCHAR(90) NOT NULL, -- 父项编码
child_id  VARCHAR(90) NOT NULL, -- 子项编码
child_desc VARCHAR(90)); -- 子项描述

INSERT INTO test_data.dbo.t_releation(parent_id, child_id, child_desc)
VALUES
('ttt', 'L000', '钟表'),
('L000', 'L001','指针'),
('L000', 'L002','电池'),
('L001', 'L003','时针'),
('L001', 'L004','分针'),
('L003', 'L005','针尖'),
('L003', 'L006','针体');
*/

-- 查出一个物料下所有的组件与零件
WITH rbom AS
(SELECT parent_id, child_id, child_desc FROM test_data.dbo.t_releation WHERE parent_id = 'L000'
UNION ALL
SELECT a.parent_id, a.child_id, a.child_desc FROM test_data.dbo.t_releation a 
INNER JOIN rbom b ON a.parent_id = b.child_id)
SELECT * FROM rbom;

-- 进阶一 查出一个物料下所有组件并且显示层级码
WITH rbom AS
(SELECT parent_id, child_id, child_desc, dept = 1 FROM test_data.dbo.t_releation WHERE parent_id = 'L000'
UNION ALL
SELECT a.parent_id, a.child_id, a.child_desc ,dept = b.dept + 1 FROM test_data.dbo.t_releation a 
INNER JOIN rbom b ON a.parent_id = b.child_id)
SELECT * FROM rbom;

-- 进阶二 只显示真实的采购物料，不显示过程中产生的组件
WITH rbom AS
(SELECT parent_id, child_id, child_desc FROM test_data.dbo.t_releation WHERE parent_id = 'L000'
UNION ALL
SELECT a.parent_id, a.child_id, a.child_desc	FROM test_data.dbo.t_releation a 
INNER JOIN rbom b ON a.parent_id = b.child_id)
SELECT a.* FROM rbom a WHERE NOT EXISTS (SELECT 1 FROM rbom b WHERE b.parent_id = a.child_id);

查询结果