SQL Server 中的递归查询 [英] Recursive query in SQL Server

问题描述

我有一个具有以下结构的表格

I have a table with following structure

表名:匹配

基本上存储哪个产品匹配哪个产品.我需要处理这张表并存储在如下所示的组表中.

That basically stores which product is matching which product. I need to process this table And store in a groups table like below.

表名:groups

group_ID 存储组成一个组的 Product_IDS 的 MIN Product_ID.举个例子让我们说

group_ID stores the MIN Product_ID of the Product_IDS that form a group. To give an example let's say

如果 A 匹配 B 并且 B 匹配 C 那么三行应该按照格式(A, A), (A, B), (A, C)

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)

我曾尝试研究相关的子查询和 CTE，但没有实现这一点.

I have tried looking into co-related subqueries and CTE, but not getting this to implement.

我需要在 SQL 中完成这一切.

I need to do this all in SQL.

感谢您的帮助.

推荐答案

试试这个:

;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

您可以使用OPTION(MAXRECURSION n)来控制递归深度.

You can use OPTION(MAXRECURSION n) to control recursion depth.

SQL 小提琴演示

这篇关于SQL Server 中的递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！