bugfix> oracle > 投稿

いくつかの属性を1つずつ照合する必要があります。複数の選択ステートメントを避けたいと思っています。以下に例を示します。

   Table1
    Col1|Price|Brand|size
    -----------------------
    A|10$|BRAND1|SIZE1
    B|10$|BRAND1|SIZE1
    C|30$|BRAND2|SIZE2
    D|40$|BRAND2|SIZE4

    Table2
    Col1|Col2|Col3
    --------------
    B|XYZ|PQR
    C|ZZZ|YYY

    Table3
    Col1|COL2|COL3|LIKECOL1|Price|brand|size
    -----------------------------------------
    B|XYZ|PQR|A|10$|BRAND1|SIZE1
    C|ZZZ|YYY|D|NULL|BRAND2|NULL

table3では、以下の条件を確認して、table2からデータを挿入する必要があります。

  1. ブランドとサイズ、価格が一致する場合、table2のレコードの一致を検索します
  2. 一致が見つからない場合は、ブランド、サイズだけを試してください
  3. まだ一致が見つかりません、ブランドのみを試してください

上記の例では、table2の最初のレコードが3つの属性すべてと一致し、table3と2番目のレコードに挿入されているため、レコード「D」は一致しますが「Brand」のみが一致します。

私が考えることができるのは、以下のような3つの異なる挿入ステートメントをoracle pl/sqlブロックに書くことです。

insert into table3 
   select from tab2 
    where all 3 attributes are matching;
 insert into table3 
   select from tab2 
    where brand and price are matching 
      and not exists in table3 (not exists is to avoid 
                                inserting the same record which was already 
                                inserted with all 3 attributes matched);
 insert into table3 
   select from tab2 
    where Brand is matching and not exists in table3;

誰でもtable2から複数回選択することを避けるために、より良い方法でそれを達成するためのより良い方法を提案してください。

回答 2 件
  • これは OUTER APPLY の場合です 。

    OUTER APPLY   FROM の前に表示されるテーブルを参照する動的ビューで結合できるラテラル結合の一種です  句。その機能を使用すると、すべての一致を検出し、指定した序列順に並べ替えてから FETCH FIRST 1 ROW ONLY を使用する動的ビューを定義できます。  結果に最初のものだけを含めるため。

    OUTER APPLY を使用する  一致するものがない場合でも、テーブルBレコードを取得します-すべての一致列 null だけで 。必要ない場合は、 OUTER APPLY を変更できます   CROSS APPLY へ 。

    Michael Piankovの答えからテーブル作成スクリプトを恥知らずに盗む、実例(ステップバイステップのコメント付き)を次に示します。

    create table Table1 (Col1,Price,Brand,size1)
    as select 'A','10','BRAND1','SIZE1' from dual union all
       select 'B','10','BRAND1','SIZE1' from dual union all
       select 'C','30','BRAND2','SIZE2' from dual union all
       select 'D','40','BRAND2','SIZE4'from dual 
    create table Table2(Col1,Col2,Col3)
    as select 'B','XYZ','PQR' from dual union all 
       select'C','ZZZ','YYY' from dual;
    -- INSERT INTO table3
    SELECT t2.col1, t2.col2, t2.col3,
    t1.col1 likecol1, 
    decode(t1.price,t1_template.price,t1_template.price, null) price,
    decode(t1.brand,t1_template.brand,t1_template.brand, null) brand,
    decode(t1.size1,t1_template.size1,t1_template.size1, null) size1
    FROM 
    -- Start with table2
    table2 t2
    -- Get the row from table1 matching on col1... this is our search template
    inner join table1 t1_template on
    t1_template.col1 = t2.col1
    -- Get the best match from table1 for our search 
    -- template, excluding the search template itself
    outer apply ( 
    SELECT * FROM table1 t1 
    WHERE 1=1
    -- Exclude search template itself
    and t1.col1 != t2.col1
    -- All matches include BRAND
    and t1.brand = t1_template.brand
    -- order by match strength based on price and size
    order by case when t1.price = t1_template.price and t1.size1 = t1_template.size1 THEN 1
    when t1.size1 = t1_template.size1 THEN 2
    else 3 END
    -- Only get the best match for each row in T2
    FETCH FIRST 1 ROW ONLY) t1;
    
    

  • 残念ながら、一致と言うときの意味は明確ではありません。一致するものが複数ある場合、何を期待しますか? 最初に一致するだけの場合、または利用可能なすべてのペアを生成する必要がありますか?

    複数の挿入を避ける方法についての質問については、複数の方法があります:

    INSERT first でマルチテーブル挿入を使用できます  および条件。

    table1をselfに結合して、すべてのペアを取得し、where条件で結果をフィルター処理できます。

    分析機能を使用できます

    別の方法があると思います。しかし、なぜ3つの単純な挿入を避けたいのでしょうか。読みやすく保守しやすい。そして多分

    次に分析機能を備えた例があります。

    create table Table1 (Col1,Price,Brand,size1)
    as select 'A','10','BRAND1','SIZE1' from dual union all
       select 'B','10','BRAND1','SIZE1' from dual union all
       select 'C','30','BRAND2','SIZE2' from dual union all
       select 'D','40','BRAND2','SIZE4'from dual 
    create table Table2(Col1,Col2,Col3)
    as select 'B','XYZ','PQR' from dual union all 
       select'C','ZZZ','YYY' from dual
    with s as (
    select Col1,Price,Brand,size1, 
            count(*) over(partition by Price,Brand,size1 ) as match3, 
            count(*) over(partition by Price,Brand ) as match2,
            count(*) over(partition by Brand ) as match1,
            lead(Col1) over(partition by Price,Brand,size1 order by Col1) as like3, 
            lead(Col1) over(partition by Price,Brand order by Col1) as like2,
            lead(Col1) over(partition by Brand order by Col1) as like1,
            lag(Col1) over(partition by Price,Brand,size1 order by Col1) as like_desc3, 
            lag(Col1) over(partition by Price,Brand order by Col1) as like_desc2,
            lag(Col1) over(partition by Brand order by Col1) as like_desc1
    from Table1 t )
    select t.Col1,t.Col2,t.Col3, coalesce(s.like3, like_desc3, s.like1, like_desc1, s.like1, like_desc1),
        case when  match3 > 1 then size1 end as size1,
        case when  match1 > 1 then Brand end as Brand,
        case when  match2 > 1 then Price end as Price
            from table2 t
             left join s on s.Col1 = t.Col1 
    COL1    COL2    COL3    LIKE_COL    SIZE1   BRAND   PRICE
    B   XYZ PQR A   SIZE1   BRAND1  10
    C   ZZZ YYY D    -  BRAND2   -
    
    

あなたの答え