人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

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

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

よろしくお願いします。

●質問者: ReoReo7
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:GROUP MySQL いもの キュー コメント欄
○ 状態 :終了
└ 回答数 : 1/1件

▽最新の回答へ

1 ● chuken_kenkou
●60ポイント ベストアンサー

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


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

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より優先なので、この括弧のペアはなくてもいい
 )
;
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ