SQL Recursive CTE с использованием двух таблиц

У меня есть две таблицы: одна — сопоставление клиентов продукта, а другая — таблица обмена продуктами. Используя рекурсивный CTE SQL, я пытаюсь найти все продукты, которые связаны как цепочка, сопоставленные с клиентами. Кроме того, если продукт используется совместно с другим продуктом, мне также необходимо включить его в цепочку. Надеюсь, мой пример имеет больше смысла, чем описание

Product Customer Table
Product    Customer
  Milk     Illinois
  Milk     Michigan
  Butter   Michigan
  Cream    Wisconsin
  Honey    Wisconsin
  Cheese   Minnesota

Product Sharing Table
Product    SharedProduct
 Butter     Cream
 Cream      Cheese

Для приведенных выше данных допустим, что мой входной продукт — молоко, тогда набор результатов должен включать все продукты — молоко, масло, сливки, мед и сыр. Здесь Butter-Cream и Cream-Cheese связаны через таблицу обмена продуктами.

Мой текущий SQL выглядит так, но на самом деле он не работает более чем на один уровень.

WITH Product_CTE AS
(
  SELECT DISTINCT [Product] FROM ProductCustomer
  WHERE [Product] IN (SELECT DISTINCT p2.[Product]
  FROM ProductCustomer p1 INNER JOIN ProductCustomer p2
  ON p1.[Customer] = p2.[Customer] WHERE p1.[Product] = 'Milk')
  UNION ALL
  SELECT [SharedProduct] FROM ProductSharing b
  INNER JOIN Product_CTE p ON p.[Product] = b.[Product]
)
Select [Product] from Product_CTE

sql common-table-expression recursive-query sql-server-2005
person user320587    schedule 22.08.2012    source источник
comment
Просто чтобы уточнить, ваш пример приходит к Honey из: Молоко › Мичиган › Масло › Сливки (через обмен) › Висконсин › Мед. Это правильно?   -  person HABO    schedule 23.08.2012

Ответы (1)


arrow_upward
0
arrow_downward

CTE имеет проблемы с несколькими UNION. Хотя это может быть возможно, это не сработало для меня.

Альтернативой является использование цикла, который останавливается, когда в рабочую таблицу не добавляются новые строки:

declare @ProductCustomers as Table ( Product VarChar(16), Customer VarChar(16) )
insert into @ProductCustomers ( Product, Customer ) values
  ( 'Milk', 'Illinois' ),
  ( 'Milk', 'Michigan ' ),
  ( 'Butter', 'Michigan ' ),
  ( 'Cream', 'Wisconsin' ),
  ( 'Honey', 'Wisconsin' ),
  ( 'Cheese', 'Minnesota' )

declare @ProductSharing as Table ( Product VarChar(16), SharedProduct VarChar(16) )
insert into @ProductSharing ( Product, SharedProduct ) values
  ( 'Butter', 'Cream ' ),
  ( 'Cream', 'Cheese ' )

declare @TargetProduct as VarChar(16) = 'Milk'

declare @ProductChain as Table ( Product VarChar(16) )
insert into @ProductChain ( Product ) values ( @TargetProduct )
declare @NewRows as Int = 1

while @NewRows > 0
  begin
  set @NewRows = 0
  -- Add products shared by the same customer.
  insert into @ProductChain
    select PCR.Product
      from @ProductCustomers as PCL inner join
        @ProductCustomers as PCR on
          -- Shared customer.
          PCR.Customer = PCL.Customer and
          -- Different product.
          PCR.Product <> PCL.Product
      where not exists ( select 42 from @ProductChain where Product = PCR.Product )
  set @NewRows = @NewRows + @@RowCount
  -- Add products linked through the product sharing table.
  insert into @ProductChain
    select PS.SharedProduct
      from @ProductSharing as PS inner join
        @ProductChain as PC on PC.Product = PS.Product
      where not exists ( select 42 from @ProductChain where Product = PS.SharedProduct )
  set @NewRows = @NewRows + @@RowCount 
  end

select Product
  from @ProductChain
  order by Product

Здесь предполагается, что таблица @ProductSharing является однонаправленной.

person HABO    schedule 22.08.2012
comment
Спасибо за код. Похоже, я не смогу сделать это в одном запросе. Поскольку мне нужно выполнить этот запрос из JAVA, поэтому мне может понадобиться сделать его SP. Спасибо еще раз. - person user320587; 23.08.2012