timestampを引算し6ヵ月以上ならフラグ=1

質問

登録されているレコードが６カ月または１２ヶ月以内か判定し、TRUEならフラグに１を記すSELECT文をご教示お願いいたします。

MySQL5です。テーブル　mytableに、
(1) myName（氏名、varchar(600)、レコード例：加藤太郎）
(2) RegistDate（INSERTした日付、timestamp、レコード例：2005-06-25 09:30:00）
と２カラムがあります。

考え方としては照会時に、
「本日のサーバーの月度　マイナス　RegistDateの月度 ≧ 7ヶ月（または13ヶ月）」なら「myFlag=1」ということになるかと思いますが・・・

select case
when to_days(now()) - to_days(RegistDate) >=7 then myFlag = '1'
from mytable

とか、
IF THENなどになろうかと思いますが、よろしくお願い致します。

回答No.3

yambejp · ベストアンサー

えーと、こんな感じでどうですか？

//元データ
create table mytable (myCode int not null auto_increment primary key,val varchar(20));
insert into mytable(val) values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i');

//最新6件にフラグをつける
select t1.*,if(isnull(t2.myCode),0,1 ) as flg from mytable as t1
left join (select myCode from mytable order by myCode desc limit 6) as t2
on t1.myCode=t2.myCode

お礼

yamabejpさん、しばらく本件から手が離れていたため、御礼大変遅くなってすみませんでした。
そして、いつも適切なご回答本当にありがとうございます、

まず、ご提示のデータ＆サンプルコードでは、バッチリで期待動作でありましたこと、ご報告します。

これをそのまま本番環境のレコードへ置き換えてphpMyAdmin上に展開してみますとものすごく重複データが出てきてしまうようです。

select t1.*,if(isnull(t2.myCode),0,1 ) as flg from mytable as t1
left join (select myCode from mytable order by myCode desc limit 6) as t2
on t1.myCode=t2.myCode

そこで、↑ではなく、さらに一歩踏み込んで本番SQLの方に適用すると、phpMyAdminからは次のようなエラーです。。。
#1054 - Unknown column 't1.myCode' in 'on clause'

select t1.*, t3.*, if(isnull(t2.myCode),0,1 ) as flg from mytable as t1, mymaster as t3 left join (select myCode from mytable order by myCode desc limit 6) as t2 on t1.myCode=t2.myCode where t1.KeyNumber = t3.KeyNumber and t1.myCode > ( select (max(myCode) - 24) from mytable ) order by t1.myCode desc

なにか初歩的なつまづきをしておりますでしょうか。。。

それで（申し訳なくて合わせる顔もないのですが）、ぼけっとしてましたが、myCodeがauto_incrementな一意キーという設計も大嘘でした。
月刊雑誌の（発行から通算）何号、という数値で、最新から6ヵ月に「NEW」画像をつけたい目的で「flg」をつけたいです。
ですので、同じmyCodeが複数のレコード行に存在しています。
したがいまして、myCodeの最大値からマイナス6号にflg＝1としたい流れでした。

本当にすみません。

補足

すみません、帰宅電車の中で思い起こしていたら
自分が書いた御礼でわけのわからないことを
書いていた気がしました。

今読み返すると、やはりわけがわからないですね。。。

既に完璧なサンプルをご提示いただいたので
一旦締め切らせていただき、再整理したうえで別質問をさせて
いただきますm(_ _)m

回答No.2

yambejp

＞冒頭の定義（'6ヵ月'でなく常に最新'6名'にフラグをつける）でしたら、

＞別途、auto_incrementな一意キー＝myCode（int(4)）に着目して
＞( select (max(myCode) - 6)
＞みたいな計算で何とかいけそうな気がしてきました。

auto_incrementは必ずしも連番であるとはかぎりませんので
order by auto_incrementのカラム desc limit 6
の方が現実的かもしれません
（これをサブクエリで連結するなりして絞り込んだりする）

きちんとやる場合は一応ランク付けのロジックなどもあるのですが・・・

＞IF THENなどになろうかと思いますが

前回例示した通り「if(条件,真の値,偽の値)」で十分では？
select if(なんたら,1,0) as flag;
とすればよいということです

なお複雑な分岐をする際は、case～when～then～end構文を使うことが多いですね

お礼

yamabejpさん、たびたびありがとうございます。

＞auto_incrementは必ずしも連番であるとはかぎりません

盲点でした、生データをインポートするとき注意したいと思います。

＞order by auto_incrementのカラム desc limit 6
＞（これをサブクエリで連結するなりして絞り込んだりする）

目からうろこのアイデアです、すごいシンプルに解決できますね。
ただ、本件は６件だけを抽出するのではなく、フラグをつけたいのが目的なので
（サブクエリで連結するなりして、とはそれを回避せよということと理解いたしましたが）
ちょっと組み込んでみました。

＞前回例示した通り「if(条件,真の値,偽の値)」で十分では？
＞select if(なんたら,1,0) as flag;

ご提示のものを元に、

select *, if(myCode > ( select (max(myCode) - 6),1,0)) as newFlg from mytable where......

というSQL文を（phpmyAdminにて）クエリーかけてみたのですが、

You have an error in your SQL syntax; check the manual that corresponds

というエラーをはきます。カッコの掛け違いとか、初歩的なつまづきだろうと
カッコのかけ方をあれこれしてみましたが、うまくいきません・・・・

文法のどこが誤ってますでしょうかm(_ _)m

回答No.1

yambejp

命題のあいまいさをつぶす必要があります

・時間の問題
時間まで比較するか
仮にデータ投入が「2005-06-25 09:30:00」だったとき
半年後とは「2005-12-25 09:30:00」ないし「2005-06-25 09:30:01」以降
とすべきかどうかです。

通常半年後的な計算をする場合は
「2005-06-25 09:30:00」を「2005-06-25」と日付だけ抽出し
「2005-12-25」ないし「2005-06-26」を対象日とすると思います

そのあたりのブレを調整してください

・小の月の対応
仮に時間を省いたとして「2005-02-28」の半年後を「2005-08-28」だとして
その翌日「2005-03-01」の半年後を「2005-09-01」になると思います
すると8/29～31が浮いてしまいますが、よろしいですか？
逆に「2004-08-28」の半年後は「2005-02-28」ですが、
「2004-08-29」～「2004-09-01」の半年後はすべて「2005-03-01」になります
そんなロジックでよろしいのでしょうか？

とりあえず考え方としては
if(RegistDate + interval 6 month < now(),0,1)
的な書き方になると思います

お礼

yamabejpさん、いつもお世話になります。

以下、お返事で論点を変えてしまってまずはお詫び申し上げますが・・・

> ・時間の問題
> 時間まで比較するか
> 仮にデータ投入が「2005-06-25 09:30:00」だったとき
> 半年後とは「2005-12-25 09:30:00」ないし「2005-06-25 09:30:01」以降
> とすべきかどうかです。

「半年後」という時間軸での要望の仕方が悪かったです。
myFlag = '1'　がつく行が常に最新6名になるようにすれば目的は達せられます。

if ($myFlag = 1 ){
echo "<img src='./image/新着会員.gif'>";
}

みたいな使い方をしたかった次第です。

> 通常半年後的な計算をする場合は
　　：
> ・小の月の対応

いやはや、、、（毎度のことながら）大変勉強になります。
Excelの時給計算などもいろいろ気配りが必要なので「日時の計算は難しい」という
先入観がありますが、やはり厳密な「ブレの調整」が必要ですね。

> とりあえず考え方としては
> if(RegistDate + interval 6 month < now(),0,1)
> 的な書き方になると思います

冒頭の定義（'6ヵ月'でなく常に最新'6名'にフラグをつける）でしたら、

別途、auto_incrementな一意キー＝myCode（int(4)）に着目して
( select (max(myCode) - 6)
みたいな計算で何とかいけそうな気がしてきました。
IF　と　as myFlag = '1' の付け加え方（書き方、文法）はどのようになるでしょうか？m(_ _)m