SQL Server 中的递归查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14518090/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Recursive query in SQL Server
提问by Ankit
I have a table with following structure
我有一个具有以下结构的表
Table name: matches
表名： matches
That basically stores which product is matching which product. I need to process this table And store in a groups table like below.
这基本上存储哪个产品匹配哪个产品。我需要处理这张表并将其存储在如下所示的组表中。
Table Name: groups
表名： groups
group_IDstores the MIN Product_IDof the Product_IDSthat form a group. To give an example let's say
group_ID存储所述MIN Product_ID的Product_IDS形成一组。举个例子让我们说
If A is matching B and B is Matching C then three rows should go to group table in format (A, A), (A, B), (A, C)
如果 A 与 B 匹配，B 与 C 匹配，则三行应按格式转到分组表 (A, A), (A, B), (A, C)
I have tried looking into co-related subqueries and CTE, but not getting this to implement.
我曾尝试研究相关的子查询和 CTE，但没有实现这一点。
I need to do this all in SQL.
我需要在 SQL 中完成这一切。
Thanks for the help .
谢谢您的帮助 。
回答by Hamlet Hakobyan
Try this:
尝试这个：
;WITH CTE
AS
(
SELECT DISTINCT
M1.Product_ID Group_ID,
M1.Product_ID
FROM matches M1
LEFT JOIN matches M2
ON M1.Product_Id = M2.matching_Product_Id
WHERE M2.matching_Product_Id IS NULL
UNION ALL
SELECT
C.Group_ID,
M.matching_Product_Id
FROM CTE C
JOIN matches M
ON C.Product_ID = M.Product_ID
)
SELECT * FROM CTE ORDER BY Group_ID
You can use OPTION(MAXRECURSION n)to control recursion depth.
您可以使用OPTION(MAXRECURSION n)来控制递归深度。
回答by a_horse_with_no_name
Something like this (not tested)
像这样的东西（未测试）
with match_groups as (
select product_id,
matching_product_id,
product_id as group_id
from matches
where product_id not in (select matching_product_id from matches)
union all
select m.product_id, m.matching_product_id, p.group_id
from matches m
join match_groups p on m.product_id = p.matching_product_id
)
select group_id, product_id
from match_groups
order by group_id;
回答by Wander César Louren?o
Sample of the Recursive Level:
递归级别的示例：
DECLARE @VALUE_CODE AS VARCHAR(5);
--SET @VALUE_CODE = 'A' -- Specify a level
WITH ViewValue AS
(
SELECT ValueCode
, ValueDesc
, PrecedingValueCode
FROM ValuesTable
WHERE PrecedingValueCode IS NULL
UNION ALL
SELECT A.ValueCode
, A.ValueDesc
, A.PrecedingValueCode
FROM ValuesTable A
INNER JOIN ViewValue V ON
V.ValueCode = A.PrecedingValueCode
)
SELECT ValueCode, ValueDesc, PrecedingValueCode
FROM ViewValue
--WHERE PrecedingValueCode = @VALUE_CODE -- Specific level
--WHERE PrecedingValueCode IS NULL -- Root
