指定した年に在籍していた社員を抽出する

質問

社員のデータをテーブルに格納しました。
テーブルは下記のようになっています。inが入社年。outが退社年です。退社年が空白の社員は在籍中を表しています。

テーブル名：office

nama(VARCHAR型)｜in(INT型)｜out(INT型)
田中｜2003｜2008
加藤｜1995｜2001
桐谷｜2009｜

やりたい事が二点あります。
(1)「2007年に在籍した社員」と指定した場合。上のテーブルでいうと田中のデータを抽出。
「in<2007 AND out>2007」このような指定をすればいいのかと思ったのですが、空白(現在も在籍中)の場合どうすればいいのか分かりません。

(2)「在籍年数が4年以上6年未満の社員」と期間を指定した場合。上のテーブルでいうと田中と桐谷のデータを抽出。

この場合SQL文はどのようにすれば良いでしょうか？
このテーブルのデータの書き方だと難しいでしょうか？難しければもっとこうした方が良いなどアドバイスを頂けると幸甚です。

ご存知の方いらっしゃいましたらご教示ください。
よろしくお願いします。

回答No.3

yambejp · ベストアンサー

create table office(name varchar(10),`in` int,`out` int default 2999);
insert into office values('田中',2003,2008),('加藤',1995,2001),('桐谷',2009,default)

//2007年在籍
select * from office where 2007 between `in` and `out`; //田中さん

//2010年在籍
select * from office where 2010 between `in` and `out`; //桐谷さん

//在籍年数が4年以上6年未満の社員
select * from office where least(year(now()),`out`)-`in`+1 between 4 and 6 ; //田中さんと桐谷さん

お礼

回答ありがとうございます。

データはphpmyadminで入力しているのですが、デフォルト値を「2999」に設定しても上手く反映されず、結局在籍中の社員は「out」の数字を「2999」と直接入力しました。

そしてご教示頂いたSQL文で実行してみたら無事できました。
色々とありがとうございました。勉強になりました。

回答No.4

yambejp

あ、以上・以下ではなくより大きい・より小さい（未満）のときは
1足したり1引いたりして調整してください

回答No.2

yambejp

こういうのはoutのデフォルト値を2999などにしておけば済む話です
そうでないばあいintのデフォは0になるのでこれもあまり美しくない
またint値にnullを生かすのは可能な限りやめたほうがいい

ちなみに年だけで期間を表すと月ズレするのであまりいい管理方法とは言えません

＞「在籍年数が4年以上6年未満の社員」

この条件は「在籍年数が4年以上6年未満の『すでにやめた』社員」と
「在籍年数が4年以上6年未満の『在籍中の』社員」に分けられますが
別に管理しなくてもいいんでしょうか？

お礼

回答ありがとうございます。

＞こういうのはoutのデフォルト値を2999などにしておけば済む話です

そのようにしました。
色々勉強になりました。ありがとうございました。

回答No.1

t_ohta

(1)
select * from office where in <= 2007 and (out >= 2007 or out is null);

(2)
select * from office where (in <= (year(now()) -4) and in > (year(now()) -6) and out is null) or ((out - in) >= 4 and (out - in) < 6);

補足

回答ありがとうございます。

(1)と(2)を先ほど実行してみたのですが、また私の説明が足りなかったかもしれません。
空白（現在在籍している社員）の場合は今年の年（2014年）が入力されているのと同じ処理ができればと思っています。

(1)ですと例に出した「2007年に在籍した社員」と指定した場合は田中のデータが抽出できるのですが、「2010年に在籍した社員」と指定した場合は現在在籍していて退社年が空白の桐谷のデータは抽出できません。
退社済みの社員のみが対象となってしまっているようです。

(2)も上記と同じです。データは抽出できるのですが、退社済みの社員が対象となってしまっています。

この問題は現在在籍している社員の退社年に今年の年を記入すれば解決すると思うのですが、別に「○○年に退社した社員」という指定で抽出もしているので在籍しているにもかかわらず退社している社員として抽出されてしまうのでできません。

こういったものはやはり無理があるでしょうか？
よろしくお願いします。