Рекурсивная sql проблема

Редактирование: , Пока я думаю, что мой хорошо работает, ответ Adam без сомнения, более изящного и более эффективного - я просто оставлю мой здесь для потомства!

Извинения, так как я знаю это, были отмечены как проблема Oracle, так как я начал играть. Это - некоторый код SQL2008, который я думаю работы для всего изложившего свои доводы....

declare @test table
(
    [PI] int
)
insert @test values (10), (11), (13)

declare @testCount int
select @testCount = COUNT(*) from @test

;with PO_WITH_COUNTS as 
(
        select  PO_FULL.PO, COUNT(PO_FULL.[PI]) PI_Count
        from    ProductOffering PO_FULL
        left
        join    (
                select  PO_QUALIFYING.PO, PO_QUALIFYING.[PI]
                from    ProductOffering PO_QUALIFYING
                where   PO_QUALIFYING.[PI] in (select [PI] from @test)
                ) AS QUALIFYING
                on      QUALIFYING.PO = PO_FULL.PO
                and     QUALIFYING.[PI] = PO_FULL.[PI]
        group by
                PO_FULL.PO
        having  COUNT(PO_FULL.[PI]) = COUNT(QUALIFYING.[PI])
)
select  PO_OUTER.PO
from    PO_WITH_COUNTS PO_OUTER 
cross 
join    PO_WITH_COUNTS PO_INNER
where   PO_OUTER.PI_Count = @testCount
or      PO_OUTER.PO <> PO_INNER.PO
group by
        PO_OUTER.PO, PO_OUTER.PI_Count
having  PO_OUTER.PI_Count = @testCount 
or      PO_OUTER.PI_Count + SUM(PO_INNER.PI_Count) = @testCount

Не уверенный, если Oracle имеет CTEs, но могла бы просто заявить внутренний запрос как две полученных таблицы. Перекрестное объединение во внешнем запросе позволяет нам найти комбинации предложений, которые имеют все допустимые объекты. Я знаю, что это будет только работать на основе оператора в вопросе, что данные таковы, что существует только 1 допустимая комбинация для каждого требуемого набора, Без которого это еще более сложно, поскольку количеств недостаточно для удаления комбинаций, которые имеют дублирующиеся продукты в них.

У меня нет дб передо мной, но первое, что пришло на ум Вы хотите список POs, которые не имеют никаких ПИ не в Вашем входном списке, т.е.

select distinct po 
from tbl 
where po not in ( select po from tbl where pi not in (10,11,13) )

Редактирование: Вот является пример другими случаями:
, Когда введенный PI = 10,11,13 внутренний выбор возвращается, таким образом, внешний выбор возвращает B, C
, Когда введенный PI = 10 внутренний выбор возвращает A, B, C, таким образом, внешний выбор не возвращает строк
, Когда введенный PI = 10,11,12 внутренний выбор возвращает C, таким образом, внешний выбор возвращает A, Редактирование B

: Adam указал, что этот последний случай не отвечает требованию только возврата (это будет учить меня для стремительного движения), таким образом, это еще не будет работать код.

  Select Distinct PO
   From Table T
   -- Next eliminates POs that contain other PIs
   Where Not Exists 
       (Select * From Table 
        Where PO = T.PO
            And PI Not In (10, 11, 12))
     -- And this eliminates POs that do not contain all the PIs
     And Not Exists 
        (Select Distinct PI From Table  
         Where PI In (10, 11, 12)
           Except 
         Select Distinct PI From Table  
         Where PO = T.PO

или, если Ваша база данных не реализует КРОМЕ...

   Select Distinct PO
   From Table T
   -- Next predicate eliminates POs that contain other PIs
   Where Not Exists 
       (Select * From Table 
        Where PO = T.PO
            And PI Not In (10, 11, 12))
     -- And this eliminates POs that do not contain ALL the PIs
     And Not Exists 
         (Select Distinct PI From Table A
          Where PI In (10, 11, 12)
             And Not Exists
                 (Select Distinct PI From Table 
                  Where PO = T.PO 
                     And PdI = A.PI))                 

Действительно ли возможно, что клиенты просят продукт несколько раз?

, Например: он просит у предложения 10,10,11,11,12?

, Если это возможно, чем решения как

выбор... из..., где пи в (10,10,11,11,12)

не будет работать.

, поскольку 'пи в (10,10,11,11,12)' совпадает с 'пи в (10,11,12)'.

решением для А для 10,10,11,11,12 является A& B.

хорошо некоторый псевдо код от вершины моей головы здесь:

выбор из таблицы, где PI = 10 или пи =11, и т.д.

хранит результат во временной таблице

, выбирает отличную ПО и количество (PI) от временной таблицы.

теперь для каждой ПО можно получить общие доступные предложения PI. если количество ПИ доступные соответствия количество во временной таблице, это означает, что у Вас есть все ПИ для той ПО. добавьте весь POs и Вас авеню Ваш набор результатов.

Вам будет нужно количество объектов в Вашем списке, т.е. @list_count. Фигура, какие Предложения имеют Экземпляры, которые не находятся в списке. Выберите все Предложения, которые не находятся в , что список и делает , имеют Экземпляры в списке:

select P0,count(*) c from table where P0 not in (
select P0 from table where P1 not in (@list)
) and P1 in (@list) group by P0

я сохранил бы это во временной таблице и выборе * записи где c = @list_count

Если мы переопределяем немного проблема:

Позволяет, имеют клиентскую таблицу с экземплярами продукта:

crete table cust_pi (
pi varchar(5),
customer varchar(5));

И "product_catalogue" таблица:

CREATE TABLE PI_PO_TEST
   ("PO" VARCHAR2(5 CHAR),
   "PI" VARCHAR2(5 CHAR)
           );

Позволяет, заполняют его некоторыми демонстрационными данными:

insert into CUST_PI (PI, CUSTOMER)
values ('11', '1');
insert into CUST_PI (PI, CUSTOMER)
values ('10', '1');
insert into CUST_PI (PI, CUSTOMER)
values ('12', '1');
insert into CUST_PI (PI, CUSTOMER)
values ('13', '1');
insert into CUST_PI (PI, CUSTOMER)
values ('14', '1');
insert into PI_PO_TEST (PO, PI)
values ('A', '10');
insert into PI_PO_TEST (PO, PI)
values ('A', '11');
insert into PI_PO_TEST (PO, PI)
values ('A', '12');
insert into PI_PO_TEST (PO, PI)
values ('A', '13');
insert into PI_PO_TEST (PO, PI)
values ('B', '14');
insert into PI_PO_TEST (PO, PI)
values ('C', '11');
insert into PI_PO_TEST (PO, PI)
values ('C', '12');
insert into PI_PO_TEST (PO, PI)
values ('D', '15');
insert into PI_PO_TEST (PO, PI)
values ('D', '14');

Затем мое первое решение для охоты похоже на это:

select po1 po /* select all product offerings that match the product definition 
                (i.e. have the same number of product instances per offering as 
                in product catalogue */
  from (select po po1, count(c.pi) k1
          from cust_pi c, pi_po_test t
         where c.pi = t.pi
           and customer = 1
         group by po) t1,
       (select po po2, count(*) k2 from pi_po_test group by po) t2
 where k1 = k2
   and po1 = po2
minus /* add those, that are contained within others */
select slave
  from (select po2 master, po1 slave
  /* this query returns, that if you have po "master" slave should be removed from result, 
     as it is contained within*/
          from (select t1.po po1, t2.po po2, count(t1.po) k1
                  from pi_po_test t1, pi_po_test t2
                 where t1.pi = t2.pi
                 group by t1.po, t2.po) t1,
               (select po, count(po) k2 from pi_po_test group by po) t2
         where t1.po2 = t2.po
           and k1 < k2)
 where master in
 /* repeated query from begining. This could be done better :-) */
       (select po1 po
          from (select po po1, count(c.pi) k1
                  from cust_pi c, pi_po_test t
                 where c.pi = t.pi
                   and customer = 1
                 group by po) t1,
               (select po po2, count(*) k2 from pi_po_test group by po) t2
         where k1 = k2
           and po1 = po2)

Все это было сделано на Oracle, таким образом, Ваш пробег может варьироваться

Я протестировал это под 4 множествами значений, и они все возвратили корректный результат. Это использует функцию, которую я использую в SQL для генерации таблицы от строки параметров, разделенных точками с запятой.

DECLARE @tbl TABLE (
    po varchar(10),
    pii int)

INSERT INTO @tbl
SELECT 'A', 10
UNION ALL
SELECT 'A', 11
UNION ALL
SELECT 'A', 12
UNION ALL
SELECT 'B', 10
UNION ALL
SELECT 'B', 11
UNION ALL
SELECT 'C', 13

DECLARE @value varchar(100)
SET @value = '10;11;12;'
--SET @value = '11;10;'
--SET @value = '13;'
--SET @value = '10;'

SELECT DISTINCT po
FROM @tbl a
INNER JOIN fMultiValParam (@value) p ON
a.pii = p.paramid
WHERE a.po NOT IN (
    SELECT t.po
    FROM @tbl t
    LEFT OUTER JOIN (SELECT *
            FROM @tbl tt
            INNER JOIN fMultiValParam (@value) p ON
            tt.pii = p.paramid) tt ON
    t.pii = tt.pii
    AND t.po = tt.po
    WHERE tt.po IS NULL)

вот функция

CREATE    FUNCTION [dbo].[fMultiValParam]
(@Param varchar(5000))
RETURNS @tblParam TABLE (ParamID varchar(40))
AS
BEGIN

IF (@Param IS NULL OR LEN(@Param) < 2)
BEGIN
    RETURN
END

DECLARE @len INT
DECLARE @index INT
DECLARE @nextindex INT

SET @len = DATALENGTH(@Param)
SET @index = 0
SET @nextindex = 0

WHILE (@index < @len)
BEGIN
    SET @Nextindex = CHARINDEX(';', @Param, @index)

    INSERT INTO @tblParam
    SELECT SUBSTRING(@Param, @index, @nextindex - @index)

    SET @index = @nextindex + 1

END
RETURN
END

Попробуйте это:

SELECT DISTINCT COALESCE ( offer, NULL )
FROM products
WHERE instance IN ( @instancelist )

По моему скромному мнению, невозможный через чистый SQL без некоторого кода хранимой процедуры. Но... я не уверен.

Добавленный: , С другой стороны, я понимаю о рекурсивном запросе (в 2005 MSSQL существует такая вещь, которая позволяет Вам присоединяться к запросу со своими собственными результатами, пока больше нет возвращенных строк), который мог бы "собрать" корректные ответы путем перекрестного присоединения к результатам предыдущего шага со всеми продуктами и затем отфильтровывания недопустимых комбинаций. Вы однако получили бы все перестановки допустимых комбинаций, и это едва будет эффективно. И представление является довольно смутным, таким образом, я не могу гарантировать, что оно может на самом деле быть реализовано.