以下に示す動作をするMySQLのキューを書いて下さい。


今、1つの品物に対して
uniquekey code group phase
という4つのステータスが割り当てられ、uniquekey順に登録されているとします。

ここで、「groupが同一で、かつcodeも同一の品」の中で、「phaseが1でないものが1つでもあればそれ以外のphaseを全て-1」、「phaseが全て1であれば、uniquekeyが一番大きな以外のphaseを全て-1」にするというプログラムです。

例をコメント欄に記載させて頂きました。
以前質問させて頂いた時にもすばらしい回答を頂きましたので、今回も期待しています(今回は少し簡単かな?)

よろしくお願いします。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/03/23 21:49:59
  • 終了:2011/03/30 21:50:02

ベストアンサー

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542011/03/30 15:46:43

ポイント60pt

提示された要件が限定的で、もっと詳しい要件が分かれば、もっとベターな書き方があるかも知れませんが。。。


テストで用いた定義&データ

drop table if exists `t1`;
create table `t1`
(`uniquekey`     int primary key auto_increment
,`code`          varchar(8)
,`group`         varchar(3)
,`phase`         int
);

truncate table `t1`;

insert into `t1` values
-- 例1のテストデータ
 (null,'code1','A',1)
,(null,'code2','A',1)
,(null,'code1','B',1)
,(null,'code1','A',2)
,(null,'code2','B',1)
,(null,'code1','A',1)
-- 例2のテストデータ
,(null,'code10','A',1)
,(null,'code20','A',1)
,(null,'code10','B',1)
,(null,'code10','A',1)
,(null,'code20','B',1)
,(null,'code10','A',1)
-- 追加のテストデータ
,(null,'codeA','A',1)
,(null,'codeA','B',1)
,(null,'codeB','A',2)
,(null,'codeB','B',2)
,(null,'codeC','A',1)
,(null,'codeC','A',1)
,(null,'codeC','A',1)
,(null,'codeC','B',2)
,(null,'codeC','B',2)
,(null,'codeC','B',2)
,(null,'codeC','C',1)
,(null,'codeC','C',3)
,(null,'codeC','C',5)
,(null,'codeC','D',2)
,(null,'codeC','D',4)
,(null,'codeC','D',6)
;

今回の要件を実装する上で、基本となるselect文

-- 今回の実装で、基本とする検索
select
  `group`
 ,`code`
 ,sum(`phase`=1)  as sum_p1         -- phase=1の行数
 ,sum(`phase`<>1) as sum_oth        -- phase<>1の行数
 ,max(`uniquekey`) as max_uk        -- uniquekeyの最大値
 ,count(distinct `phase`) as rcnt   -- phaseの値の種類
 from `t1`
 group by `group`,`code`
;

まずは、例2だけの実装

-- -------------------------------------------------------
-- phaseの値がすべて1なら、一番uniquekeyが大きい行以外に対し、
-- phaseを減算でなく-1にする?
-- -------------------------------------------------------
update
 `t1` as x
,(
  -- phaseの値が1種類しかないもの
  select
    `group`
   ,`code`
   ,sum(`phase`=1)  as sum_p1         -- phase=1の行数
   ,sum(`phase`<>1) as sum_oth        -- phase<>1の行数
   ,max(`uniquekey`) as max_uk
   from `t1`
   group by `group`,`code`
    having count(distinct `phase`)=1  -- phaseの値は、1種類
 ) as y
 set `phase`=-1
 where x.`group`=y.`group`
   and x.`code`=y.`code`
   and x.`uniquekey`<y.max_uk     -- uniquekeyが最大の行以外
   and y.sum_p1>1                 -- phase=1が2行以上ある
;

次に、例1だけの実装

-- -------------------------------------------------------
-- phase=1でない行がグループ内に1行でもあれば、
-- phase=1の行のphaseを減算でなく-1にする?
-- すべてphase<>1の場合は、更新対象外?
-- -------------------------------------------------------
update
 `t1` as x
,(
  -- phaseの値が2種類以上あるもの
  select
   `group`
  ,`code`
  ,sum(`phase`=1)  as sum_p1         -- phase=1の行数
  ,sum(`phase`<>1) as sum_oth        -- phase<>1の行数
  from `t1`
  group by `group`,`code`
   having count(distinct `phase`)>1  -- phaseの値は、2種類以上
 ) as y
 set x.`phase`=-1
 where x.`group`=y.`group`
   and x.`code`=y.`code`
   and sum_oth>0                     -- phase<>1の行がある
   and sum_p1>0                      -- 「phase=1の行がある」という条件の要否は?
   and x.`phase`=1                   -- phase=1の行を更新
;

最後に、例1と例2を1SQLで実装。

確認するには、テストデータを初期状態に戻してから実施してください。

-- -------------------------------------------------------
-- 二つの要件を1SQLで実装
-- -------------------------------------------------------
update
 `t1` as x
,(
  select
   `group`
  ,`code`
  ,sum(`phase`=1)  as sum_p1         -- phase=1の行数
  ,sum(`phase`<>1) as sum_oth        -- phase<>1の行数
  ,max(`uniquekey`) as max_uk
  ,count(distinct `phase`) as rcnt   -- phaseの値の種類
  from `t1`
  group by `group`,`code`
 ) as y
 set x.`phase`=-1
 where x.`group`=y.`group`
   and x.`code`=y.`code`
   and ((rcnt=1                         -- phaseの値がすべて同じ
         and x.`uniquekey`<y.max_uk     -- uniquekeyが最大の行以外
         and y.sum_p1>1                 -- phase=1が2行以上ある
        )                               -- and は orより優先なので、この括弧のペアはなくてもいい
        or
        (rcnt>1                         -- phaseの値が2種類以上
         and sum_oth>0                  -- phase<>1の行がある
         and sum_p1>0                   -- 「phase=1の行がある」という条件の要否は?
         and x.`phase`=1                -- phase=1の行を更新
        )                               -- and は orより優先なので、この括弧のペアはなくてもいい
       )
;
  • id:ReoReo7
    例1、「groupが同一で、かつcodeも同一の品」の中で、「phaseが1でないものが1つでもあればそれ以外のphaseを全て-1」
    uniquekey code group phase
    1 code1 A 1
    2 code2 A 1
    3 code1 B 1
    4 code1 A 2
    5 code2 B 1
    6 code1 A 1
    とすると、
    uniquekey code group phase
    1 code1 A -1
    2 code2 A 1
    3 code1 B 1
    4 code1 A 2
    5 code2 B 1
    6 code1 A -1
    となればOKです。

    例2、「phaseが全て1であれば、uniquekeyが一番大きな以外のphaseを全て-1」
    uniquekey code group phase
    1 code1 A 1
    2 code2 A 1
    3 code1 B 1
    4 code1 A 1
    5 code2 B 1
    6 code1 A 1
    とすると、
    uniquekey code group phase
    1 code1 A -1
    2 code2 A 1
    3 code1 B 1
    4 code1 A -1
    5 code2 B 1
    6 code1 A 1
    となればOKです。
  • id:chuken_kenkou
    MySQLのバージョンを、最低限でMySQL 4.1、5.0、5.1といったレベルまで提示するようにしてください。

    MySQL 4.0まで・・・一部のジョイン、union、一時表などは実装済み
    MySQL 4.1・・・ジョインの拡張、サブクエリ、unicodeの実装
    MySQL 5.0・・・ビュー表、ストアドプロシジャ、トリガの実装

    など、大きな機能拡張や一部の仕様変更があります。

    また、MySQL 5.1では、「MySQL 5.1.xで仕様変更」などというのもいくつかあります。
  • id:ReoReo7
    ありがとうございます。

    # サーバのバージョン: 5.1.22-rc
    # プロトコルバージョン: 10
    # サーバ: Localhost via UNIX socket

    のとおりです。これで大丈夫でしょうか?
  • id:ReoReo7
    ご連絡ありがとうございます。

    すばらしい回答をありがとうございました。
    例1、例2のように設計して、最後に組み合わせると良いのですね。

    例1、例2と順を追って理解して、SQLについての理解を深めたいと思います。

    ありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません