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

У меня нет базы данных передо мной, но, в первую очередь, вы хотите, чтобы список заказов на поставку, у которых нет никаких PI, нет в вашем списке ввода, т.е.

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

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

Обновлено: Адам указал, что этот последний случай не соответствует требованию возврата только A (который научит меня спешить), поэтому это еще не рабочий код.

ну какой-то псевдокод из моей головы здесь:

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

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

выберите отдельный заказ на поставку и количество (PI) из временной таблицы.

теперь для каждого заказа на покупку вы можете получить все доступные предложения PI. если количество доступных PI совпадает с количеством в таблице temp, это означает, что у вас есть все PI для этого заказа на поставку. добавьте все заказы на покупку, и вы получите результат.

ИМХО невозможно через чистый SQL без кода хранимой процедуры. Но я не уверен.

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

  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

или, если ваша база данных не поддерживает EXCEPT ...

   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))                 

Вам понадобится количество элементов в вашем списке, то есть @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

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

Прошу прощения, так как я знаю, что это было помечено как проблема 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 CTE, но может просто указать внутренний запрос как две производные таблицы. Перекрестное соединение во внешнем запросе позволяет нам находить комбинации предложений, в которых есть все допустимые элементы. Я знаю, что это будет работать только на основе утверждения в вопросе о том, что данные таковы, что существует только одна допустимая комбинация для каждого запрошенного набора, без этого это еще более сложно, поскольку подсчетов недостаточно для удаления комбинаций, в которых есть повторяющиеся продукты в их.

Если немного переопределить проблему:

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

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, поэтому ваш опыт может отличаться

Ладно, думаю, она у меня есть. Это соответствует указанным вами ограничениям. Возможно, есть способ еще больше упростить это, но он немного съел мой мозг:

select distinct PO 
from POPI x 
where 
  PO not in (
    select PO 
    from POPI 
    where PI not in (10,11,12)
  ) 
  and PI not in (
    select PI 
    from POPI 
    where PO != x.PO 
      and PO not in (
        select PO 
        from POPI 
        where PI not in (10,11,12)
      )
  );

Это дает только результаты, которые заполняют данный набор, которые не пересекаются со всеми другими результатами, которые я считать - это то, что вы просили. Для приведенных тестовых примеров:

• Обеспечивая 10,11,12 выходов A
• Обеспечение 10,11,13 выходов B, C

Я проверил это с 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 )

Возможно ли, что покупатель запросит товар более одного раза?

Например: он / она просит пожертвовать 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.