トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-169 having句にこだわったupdate文

SQLパズル

仕入テーブル
商品番号      仕入日   販売金額
--------  ----------  --------
       1  2007-07-01         0
       1  2007-08-01         0
       1  2007-09-01         0
       2  2007-07-01         0
       2  2007-08-01         0
       3  2007-01-01         0
       3  2007-02-01         0

販売テーブル
商品番号      販売日   金額
--------  ----------  ----
       1  2007-08-10    15
       1  2007-09-10    30
       2  2007-07-15    20
       3  2007-03-01    50

販売テーブルの1レコードにつき、金額列で
仕入テーブルの1レコードを更新する。

ただし、同じ商品番号で、
仕入日が販売テーブルの販売日より、過去で最新のレコードが更新対象とする。

更新結果
商品番号      仕入日   販売金額
--------  ----------  --------
       1  2007-07-01        15
       1  2007-08-01        30
       1  2007-09-01         0
       2  2007-07-01        20
       2  2007-08-01         0
       3  2007-01-01         0
       3  2007-02-01        50

こちらを参考にさせていただきました


データ作成スクリプト

create table 仕入(
商品番号 char(1),
仕入日   date,
販売金額 number(2),
primary key(商品番号, 仕入日));

create table 販売(
商品番号 char(1),
販売日   date,
金額     number(2),
primary key(商品番号,販売日));

insert all
into 仕入 values(1,to_date('2007-07-01','yyyy-mm-dd'), 0)
into 仕入 values(1,to_date('2007-08-01','yyyy-mm-dd'), 0)
into 仕入 values(1,to_date('2007-09-01','yyyy-mm-dd'), 0)
into 仕入 values(2,to_date('2007-07-01','yyyy-mm-dd'), 0)
into 仕入 values(2,to_date('2007-08-01','yyyy-mm-dd'), 0)
into 仕入 values(3,to_date('2007-01-01','yyyy-mm-dd'), 0)
into 仕入 values(3,to_date('2007-02-01','yyyy-mm-dd'), 0)
into 販売 values(1,to_date('2007-08-10','yyyy-mm-dd'),15)
into 販売 values(1,to_date('2007-09-10','yyyy-mm-dd'),30)
into 販売 values(2,to_date('2007-07-15','yyyy-mm-dd'),20)
into 販売 values(3,to_date('2007-03-01','yyyy-mm-dd'),50)
select 1 from dual;
commit;


SQL

--■■■/*+ BYPASS_UJVC */を使う方法■■■
update (select /*+ BYPASS_UJVC */
               a.販売金額,b.金額
          from 仕入 a,販売 b
         where a.商品番号 = b.商品番号
           and a.仕入日 = (select max(c.仕入日)
                             from 仕入 c
                            where c.商品番号 = a.商品番号
                              and c.仕入日 < b.販売日))
set 販売金額 = 金額;

--■■■having句にこだわった方法■■■
update 仕入 a
   set 販売金額 = (select c.金額
                     from 仕入 b,販売 c
                    where a.商品番号 = all(b.商品番号,c.商品番号)
                      and b.仕入日 < c.販売日
                    group by c.商品番号,c.販売日,c.金額
                    having a.仕入日 = max(b.仕入日))
where (商品番号,仕入日) in (select c.商品番号,max(b.仕入日)
                              from 仕入 b,販売 c
                             where b.商品番号 = c.商品番号
                               and b.仕入日 < c.販売日
                            group by c.商品番号,c.販売日);

--■■■さらにhaving句にこだわった方法■■■
update 仕入 a
   set 販売金額 = (select c.金額
                     from 仕入 b,販売 c
                    where a.商品番号 = all(b.商品番号,c.商品番号)
                      and b.仕入日 < c.販売日
                   group by c.商品番号,c.販売日,c.金額
                   having a.仕入日 = max(b.仕入日))
 where exists     (select 1
                     from 仕入 b,販売 c
                    where a.商品番号 = all(b.商品番号,c.商品番号)
                      and b.仕入日 < c.販売日
                   group by c.商品番号,c.販売日,c.金額
                   having a.仕入日 = max(b.仕入日));

--■■■さらに変形して、group by句にRowIDを使った方法■■■
update 仕入 a
   set 販売金額 = (select c.金額
                     from 仕入 b,販売 c
                    where a.商品番号 = all(b.商品番号,c.商品番号)
                      and b.仕入日 < c.販売日
                   group by c.RowID,c.金額
                   having a.仕入日 = max(b.仕入日))
 where exists     (select 1
                     from 仕入 b,販売 c
                    where a.商品番号 = all(b.商品番号,c.商品番号)
                      and b.仕入日 < c.販売日
                   group by c.RowID
                   having a.仕入日 = max(b.仕入日));


解説

Having句の力がよく分かるupdate文だと思います。

10-200 having句にこだわったexists述語

SQL で考える HAVING句の力
集合で考える HAVING句の力:その2

CodeZine:HAVING句の力
CodeZine:帰ってきたHAVING句