今、1つの品物に対して
uniquekey code group phase
という4つのステータスが割り当てられ、uniquekey順に登録されているとします。
ここで、「groupが同一で、かつcodeも同一の品」の中で、「phaseが1でないものが1つでもあればそれ以外のphaseを全て-1」、「phaseが全て1であれば、uniquekeyが一番大きな以外のphaseを全て-1」にするというプログラムです。
例をコメント欄に記載させて頂きました。
以前質問させて頂いた時にもすばらしい回答を頂きましたので、今回も期待しています(今回は少し簡単かな?)
よろしくお願いします。
提示された要件が限定的で、もっと詳しい要件が分かれば、もっとベターな書き方があるかも知れませんが。。。
テストで用いた定義&データ
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より優先なので、この括弧のペアはなくてもいい ) ;
コメント(4件)
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です。
MySQL 4.0まで・・・一部のジョイン、union、一時表などは実装済み
MySQL 4.1・・・ジョインの拡張、サブクエリ、unicodeの実装
MySQL 5.0・・・ビュー表、ストアドプロシジャ、トリガの実装
など、大きな機能拡張や一部の仕様変更があります。
また、MySQL 5.1では、「MySQL 5.1.xで仕様変更」などというのもいくつかあります。
# サーバのバージョン: 5.1.22-rc
# プロトコルバージョン: 10
# サーバ: Localhost via UNIX socket
のとおりです。これで大丈夫でしょうか?
すばらしい回答をありがとうございました。
例1、例2のように設計して、最後に組み合わせると良いのですね。
例1、例2と順を追って理解して、SQLについての理解を深めたいと思います。
ありがとうございました。