sql - 使用递归查询的存储过程
问题描述
我正在编写一个存储过程,它应该返回结果,并且相同的结果将作为输入传递给存储过程,并且在任何特定时间输入和输出相同后递归应该停止。
下面是存储过程/函数和运行命令(存储过程的新功能)。
CREATE FUNCTION dbo.Ancestors (@List VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
WITH CTE AS
(
SELECT DISTINCT Child AS RESULT FROM example WHERE Parent IN( SELECT Id =
Item FROM dbo.SplitInts(@List, ',')) or child IN (SELECT Id = Item FROM
dbo.SplitInts(@List, ','))
UNION ALL
SELECT DISTINCT Parent AS RESULT FROM example WHERE Parent IN( SELECT Id =
Item FROM dbo.SplitInts(@List, ',')) or child IN (SELECT Id = Item FROM
dbo.SplitInts(@List, ','))
)
SELECT RESULT FROM CTE
UNION
SELECT RESULT FROM CTE
GO
CREATE PROCEDURE GetAncestors (@thingID VARCHAR(MAX))
AS
SELECT RESULT FROM dbo.Ancestors(@thingID)
GO
EXEC GetAncestors @thingID = '100'
结果是 - '100,101'
EXEC GetAncestors @thingID = '100,101'
结果是 - '100,101,102'
EXEC GetAncestors @thingID = '100,101,102'
结果是 - '100,101,102'
我实际上正在寻找的是通过传递 100 或 101 或 102 一次性给出结果
结果应该是 - '100,101,102'
下面是示例表:
| Parent | Child |
|---------------------|------------------|
| 100 | 101 |
|---------------------|------------------|
| 101 | 102 |
|---------------------|------------------|
解决方案
看起来您需要一个简单的递归查询,从给定节点开始向下遍历树。CTE
返回一个 pair 列表,所以我将(Parent, Child)
它们联合在一起以获得单个节点的列表(并在此过程中删除重复项)。
请注意,查询是递归的。CTE(公用表表达式)引用自身。
样本数据
我添加了几行以实际查看发生了什么。
DECLARE @T TABLE (Parent int, Child int);
INSERT INTO @T VALUES
(100, 101),
(101, 102),
(102, 103),
(103, 104),
(101, 108),
(108, 109),
(208, 209),
(209, 210);
询问
WITH
CTE
AS
(
SELECT
Parent, Child
FROM @T
WHERE Parent = 100
UNION ALL
SELECT
T.Parent, T.Child
FROM
CTE
INNER JOIN @T AS T ON T.Parent = CTE.Child
)
SELECT
Parent AS Result
FROM CTE
UNION
SELECT
Child AS Result
FROM CTE
;
结果
Result
100
101
102
103
104
108
109
您可以将查询放在存储过程中,如下所示:
CREATE PROCEDURE GetAncestors(@thingID int)
AS
BEGIN
SET NOCOUNT ON;
WITH
CTE
AS
(
SELECT
Example.Parent, Example.Child
FROM Example
WHERE Parent = @thingID
UNION ALL
SELECT
Example.Parent, Example.Child
FROM
CTE
INNER JOIN Example ON Example.Parent = CTE.Child
)
SELECT
Parent AS Result
FROM CTE
UNION
SELECT
Child AS Result
FROM CTE
;
END
GO
推荐阅读
- mongodb - Mongodb:如何在 2 个字段的中断处求和?
- ruby-on-rails - 它不会重定向到 show 方法
- python - 如何在其他函数中调用 Row、Header 值 - Python/Airflow/Redshift
- react-native - 从异步函数 react-native 获取未定义
- java - 如何解决 JAXB 和 Java POJO 的问题?
- asp.net-core - 为什么不能访问静态文件?
- url - IIS web.config:将 URL 文件重写为子域
- firebase - 如何将从 Firebase 读取的日期显示为 ion-datetime 元素
- c# - NetTopologySuite.Core 1.15.3 和距离单位
- json - 以有效的方式替换整个 JSON python 中的“key”名称以获取批量数据