oracle sql -以嵌套表为列的合并表

Question

我有两个表和一个嵌套表：

1.存储有关产品的数据，包括以下列：

     ITEM - product id(key)
     STORE - store id(key)
     PRICE
     NORMAL_PRICE
     DISCOUNTS - nested table with info about discounts include columns:
             PromotionId(key)
             PromotionDescription
             PromotionEndDate
             MinQty
             DiscountedPrice
             DiscountedPricePerMida

具有新折扣的2-临时表包括列：

PROMOTIONID(key)
PRODUCTID(key)
PROMOTIONDESCRIPTION
PROMOTIONENDDATE
MINQTY
DISCOUNTEDPRICE
DISCOUNTEDPRICEPERMIDA

我需要做的是将表2合并到表1中-如果没有匹配，则插入否则忽略(当匹配是:产品id与表1和2中的产品id匹配，并且对于这个产品子表PROMOTIONID匹配表2中的PROMOTIONID )

这就是我到目前为止所学到的东西，我在使用嵌套的part - ON子句和Insert子句时遇到了困难。

MERGE INTO PRICES P
USING(SELECT * FROM TMP_PROMO)T
ON(P.ITEM=T.PRODUCTID AND P.STORE=50 AND P.DISCOUNTS.PROMOTIONID=T.PROMOTIONID)
WHEN NOT MATCHED THEN INSERT (P.DISCOUNTS)
       VALUES(T.PROMOTIONID,
              T.PROMOTIONDESCRIPTION,
              T.PROMOTIONENDDATE,
              T.MINQTY,
              T.DISCOUNTEDPRICE,
              T.DISCOUNTEDPRICEPERMIDA);

我知道这是错的，但我不知道该怎么做

示例:价格表：

row1(1,50，...，nested_table(11，...)，(12，...))

row2(2,50，...，nested_table(10，...)，(12，...))

新的促销表：

(15,1，...)

(11,1，...)

id为15的新促销将添加到row1和row2

id为11的促销活动将不会被添加

请帮帮忙，谢谢

Answer 1

你想做的并不是真正的合并。您正在不包含促销的每个记录中添加新促销。

下面是一个答案，如果您不使用嵌套表，而是使用传统的子表，yu将如何处理它。

设置(简化到最低限度)

create table ITEM
(ITEM_ID NUMBER PRIMARY KEY);

create table ITEM_PROMO
(ITEM_ID NUMBER REFERENCES ITEM(ITEM_ID),
PROMO_ID NUMBER);

create table TMP_PROMO
(PROMO_ID NUMBER);

insert into ITEM values (1);
insert into ITEM values (2);

insert into ITEM_PROMO values (1,11);
insert into ITEM_PROMO values (1,12);
insert into ITEM_PROMO values (2,10);
insert into ITEM_PROMO values (2,12);

insert into TMP_PROMO values (15);
insert into TMP_PROMO values (11);

commit;

你需要做的第一件事是找出一个商品缺少哪些促销活动。使用cross join获取所有组合，并限制特定ITEM_ID的EXISTS促销：

select ITEM.ITEM_ID, TMP_PROMO.PROMO_ID 
from ITEM cross join TMP_PROMO
where NOT EXISTS (select NULL from ITEM_PROMO where ITEM_ID = ITEM.ITEM_ID and  PROMO_ID = TMP_PROMO.PROMO_ID)
;

这正如预期的那样

   ITEM_ID   PROMO_ID
---------- ----------
         2         11 
         1         15 
         2         15

现在简单地添加这些新的促销

INSERT INTO ITEM_PROMO
select ITEM.ITEM_ID, TMP_PROMO.PROMO_ID 
from ITEM cross join TMP_PROMO
where NOT EXISTS (select NULL from ITEM_PROMO where ITEM_ID = ITEM.ITEM_ID and  PROMO_ID = TMP_PROMO.PROMO_ID)
;

这应该会给你一个在使用嵌套表时如何处理的提示(或者如何改变DB设计：)