How to Group Matched Products in SQL Server Using Recursive Queries?

SQL Server: Recursive Query for Grouping Matched Products

In a relational database table named "matches," each record represents a match between two products. The goal is to create a "groups" table that captures these matches in a hierarchical structure. Specifically, the "group_ID" column should store the minimum product ID among those that belong to the same group.

To accomplish this, we can leverage the power of recursive queries in SQL. A recursive query performs multiple rounds of operations on the same data. Here's a solution that employs a common table expression (CTE) to track recursive operations:

WITH CTE
AS
(
    SELECT DISTINCT
        M1.Product_ID AS 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

The base case of the recursion is when a product has no matches, in which case it forms its own group. This is captured in the first part of the CTE.

    SELECT DISTINCT
        M1.Product_ID AS 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

The recursive part of the CTE finds all the matching products for each group. It iteratively adds these matching products to the group.

    SELECT
        C.Group_ID,
        M.matching_Product_Id
    FROM CTE C
        JOIN matches M
            ON C.Product_ID = M.Product_ID

Finally, we can retrieve the results in hierarchical order by sorting the CTE by the "Group_ID" column.

SELECT * FROM CTE ORDER BY Group_ID

This recursive query effectively produces the desired grouping of products in the "groups" table. The hierarchical relationship is captured by the "group_ID" column, ensuring that each row reflects a distinct product group.

By leveraging the recursive capabilities of SQL Server, we can efficiently extract complex hierarchical patterns and group related data in complex database structures.

The above is the detailed content of How to Group Matched Products in SQL Server Using Recursive Queries?. For more information, please follow other related articles on the PHP Chinese website!