sql - 在 SQL 中实现递归查询

Question

我有一个关于使用递归 SQL 的问题，其中我有以下表结构

产品可以位于多个组中(为了清楚起见，我没有使用 int )

CREATE TABLE ProductGroups(ProductName nvarchar(50), GroupName nvarchar(50))

INSERT INTO ProductGroups(ProductName, GroupName) values 
('Product 1', 'Group 1'),
('Product 1', 'Group 2'),
('Product 2', 'Group 1'),
('Product 2', 'Group 6'),
('Product 3', 'Group 7'),
('Product 3', 'Group 8'),
('Product 4', 'Group 6')


+-----------+---------+
|  Product  |  Group  |
+-----------+---------+
| Product 1 | Group 1 |
| Product 1 | Group 2 |
| Product 2 | Group 1 |
| Product 2 | Group 6 |
| Product 3 | Group 7 |
| Product 3 | Group 8 |
| Product 4 | Group 6 | 
+-----------+---------+

现在的问题是我想找出所有相关产品因此，如果我通过了产品 1，那么我需要以下结果

+-----------+---------+
|  Product  |  Group  |
+-----------+---------+
| Product 1 | Group 1 |
| Product 1 | Group 2 |
| Product 2 | Group 1 |
| Product 2 | Group 6 |    
| Product 4 | Group 6 | 
+-----------+---------+

所以基本上我想首先找出产品 1 的所有组，然后对于每个组我想找出所有产品等等...

1. 产品 1 => 第 1 组、第 2 组；
2. 组 1 => 产品 1、产品 2(组 1 和产品 1 已存在，因此应避免，否则将进入无限循环)；
3. 组 2 => 产品 1(已存在，与上述相同)；
4. 产品 2 => 组 1、组 6(组 1 和产品 2 已存在)
5. 组 6 => 产品 4

Answer 1

可以通过递归查询来完成，但这并不是最佳选择，因为 SQL Server 不允许您将递归表作为集合引用。因此，您最终必须保留路径字符串以避免无限循环。如果您使用整数，则可以用 hierarchyid 替换路径字符串。

with r as (
    select ProductName Root, ProductName, GroupName, convert(varchar(max), '/') Path from ProductGroups
    union all
    select r.Root, pg.ProductName, pg.GroupName, convert(varchar(max), r.Path + r.ProductName + ':' + r.GroupName + '/')
    from r join ProductGroups pg on pg.GroupName=r.GroupName or pg.ProductName=r.ProductName
    where r.Path not like '%' + pg.ProductName + ':' + pg.GroupName + '%'
)

select distinct ProductName, GroupName from r where Root='Product 1'

http://sqlfiddle.com/#!3/a65d1/5/0