複雑なクエリ

最近の連続失敗回数が3回の登録番号を求めるクエリがわかりません。
教えてください。
DBは、MySQL version: 4.0.22で、副問合せ等は使用できません。

create table t_test(
n_no integer,-- 登録番号
n_flg integer,-- ０：成功１：失敗
d_update date-- 更新日付
);

insert into t_test (n_no, n_flg, d_update)values (1,0,'2007-06-01');
insert into t_test (n_no, n_flg, d_update)values (1,0,'2007-06-02');
insert into t_test (n_no, n_flg, d_update)values (1,0,'2007-06-03');
insert into t_test (n_no, n_flg, d_update)values (1,1,'2007-06-04');
insert into t_test (n_no, n_flg, d_update)values (1,1,'2007-06-05');
insert into t_test (n_no, n_flg, d_update)values (2,0,'2007-06-01');
insert into t_test (n_no, n_flg, d_update)values (2,0,'2007-06-02');
insert into t_test (n_no, n_flg, d_update)values (2,1,'2007-06-03');
insert into t_test (n_no, n_flg, d_update)values (2,1,'2007-06-04');
insert into t_test (n_no, n_flg, d_update)values (2,1,'2007-06-05');
insert into t_test (n_no, n_flg, d_update)values (3,0,'2007-06-03');
insert into t_test (n_no, n_flg, d_update)values (3,1,'2007-06-04');
insert into t_test (n_no, n_flg, d_update)values (3,1,'2007-06-05');
insert into t_test (n_no, n_flg, d_update)values (3,1,'2007-06-06');
insert into t_test (n_no, n_flg, d_update)values (3,0,'2007-06-07');

No.2 ベストアンサー 回答者： yambejp 回答日時： 2007/07/03 11:47

4回以上を除くのであれば、最近から過去4更新のデータをとって、

ビットをたてて計算するのかなぁ・・・
ぱっと思いついたものなので効率的かどうかはわかりません。

SET @RANK=0,@NO=NULL;
CREATE TEMPORARY TABLE `temp_t_test`
SELECT n_no,n_flg, d_update,if(@NO=`n_no`,@RANK:=@RANK+1,@RANK:=1) AS `RANK`,@NO:=n_no AS `NO`
FROM `t_test`
ORDER BY `n_no` ASC,`d_update` DESC;
SELECT `n_no` ,SUM(`n_flg`*POW(2,`RANK`-1)) AS `FLG`
FROM `temp_t_test`
WHERE 1
AND `RANK` <=4
AND `n_flg`=1
GROUP BY `n_no`
HAVING `FLG`=7;

この回答へのお礼

大変参考になりました。
どうもありがとうございます。

お礼日時：2007/07/04 09:42

No.1 回答者： yambejp 回答日時： 2007/07/02 23:20

>最近の連続失敗回数が3回

の仕様提言があいまいです。

「ここ3回の更新の際、連続3回失敗しているもの」なのでしょうか？
「最新では失敗していても成功していてもかまわないが、もっとも近い失敗をみたとき
その前回、前々回が失敗しているもの」なのでしょうか？
前者ならば日付を３つとりだせばすむのですが、後者だと、失敗した更新日まで
さかのぼらなくてはなりません。

また、３回なのか３回以上なのでしょうか？
３回はいいけど４回はだめなら４回のものを除外する処理が必要です。

さらに深読みすれば「連続失敗回数が３回」ということなら、連続して失敗していない
失敗はカウントせず、２回以上失敗した場合の連続回数が３回・・・なんてふうにも
よめます。

質問者さんがなにをもとめているわからないですが、とりあえず何もしないのも
なんなんで変数とテンポラリを使ってやってみました。
うごくとはおもいますが、精査していないので効率的ではないとおもいます。

SET @RANK=0,@NO=NULL;
CREATE TEMPORARY TABLE `temp_t_test`
SELECT n_no,n_flg, d_update,if(@NO=`n_no`,@RANK:=@RANK+1,@RANK:=1) AS `RANK`,@NO:=n_no AS `NO`
FROM `t_test`
ORDER BY `n_no` ASC,`d_update` DESC;
SELECT `n_no` ,COUNT(*) AS `COUNT`
FROM `temp_t_test`
WHERE `RANK`<=3
AND `n_flg`=1
GROUP BY `n_no`
HAVING `COUNT` =3;

この回答への補足

早速のご回答どうもありがとうございます。
おっしゃるとおり仕様提言が甘くなりまして申し訳ございません。
yambejp様のおっしゃる下記の点が不足していました。
その点を含めた場合、どうすればいいのでしょうか・・。
>ここ3回の更新の際、連続3回失敗しているもの
>３回はいいけど４回はだめなら４回のものを除外する

補足日時：2007/07/03 09:38