SQLでのデータ検索方法

以下のようにデータが格納されている
テーブルAがすでにあります。
　id　　in_time　　out_time
　1　　　　　　　　　11:10
　1　　　10:30
　1　　　　　　　　　10:15
　3　　　09:50
　1　　　09:30
　1　　　　　　　　　09:00
　1　　　08:30
　2　　　　　　　　　08:10
　1　　　　　　　　　07:50
　2　　　07:40
　1　　　07:30

ここでin_timeとout_timeの範囲が指定されたとき
範囲内でidごとにin_timeとout_timeがセットにな
っているデータのみ抽出したいのです。

例えば
in_time:7:00～10:20
out_time:8:00～11:30
と指定されたとき、検索結果を
　id　　in_time　　out_time
　1　　　　　　　　　10:15
　1　　　09:30
　1　　　　　　　　　09:00
　1　　　08:30
　2　　　　　　　　　08:10
　2　　　07:40
と表示したいのです。

プログラムで１レコードずつ処理するしかない
と思っているのですが、SQLで上記のような結果
を取得する方法はあるのでしょうか。

環境は、
RHEL 5
postgesql 8.1.9
です。

No.4 回答者： nda23 回答日時： 2009/08/17 20:51

【考え方】

in_timeから見て、idが等しく、out_timeがin_timeより大きいものの
うち、最小のものがペアとなるout_time
SELECT A.id,A.in_time,MIN(B.out_time) outtm
　　FROM (SELECT id,in_time FROM テーブルA WHERE
　　to_char(in_time,'HH24:MI') BETWEEN '07:00' AND '10:20') A
LEFT JOIN (SELECT id,out_time FROM テーブルA WHERE
　　to_char(in_time,'HH24:MI') BETWEEN '08:00' AND '11:30') B
ON A.id=B.id AND A.in_time<B.out_time
GROUP BY A.id,A.in_time

但し、上記を実行すると、下記のように出力される。
id　　in_time　　out_time
　1　　　08:30　　　09:00
　1　　　09:30　　　10:15
　2　　　07:40　　　08:10

No.3 回答者： o123459876 回答日時： 2009/08/13 13:21

postgresqlの動作環境無いのでoracleで実行しました

in_time,out_timeが文字列なら
-----------------------------------------------------------
select id,in_time,out_time from(
select id,in_time,out_time,
(select min(b.out_time) from te b where b.ID = a.ID
and coalesce(replace(a.in_TIME,':',''),'0') < coalesce(replace(b.out_TIME,':',''),'0')
) xxx,
(select max(b.in_time) from te b where b.ID = a.ID
and coalesce(replace(a.out_TIME,':',''),'0') > coalesce(replace(b.in_TIME,':',''),'0')
) yyy
from te a
)
where
(coalesce(replace(in_TIME,':',''),'0') between replace('07:00',':','') and replace('10:20',':','')
and coalesce(replace(xxx,':',''),'0') between replace('08:00',':','') and replace('11:30',':','')
)
or
(coalesce(replace(out_TIME,':',''),'0') between replace('08:00',':','') and replace('11:30',':','')
and coalesce(replace(yyy,':',''),'0') between replace('07:00',':','') and replace('10:20',':','')

"ID","IN_TIME","OUT_TIME"
"1","","10:15"
"1","09:30",""
"1","","09:00"
"1","08:30",""
"2","","08:10"
"2","07:40",""
-----------------------------------------------------------
in_time,out_timeがdata型なら

select id,in_time,out_time from(
select id,in_time,out_time,
(select min(coalesce(replace(to_char(b.out_time,'hh:mi'),':',''),'0') ) from te2 b where b.ID = a.ID
and coalesce(replace(to_char(a.in_TIME,'hh:mi'),':',''),'0') < coalesce(replace(to_char(b.out_TIME,'hh:mi'),':',''),'0')
) xxx,
(select max(coalesce(replace(to_char(b.in_time,'hh:mi'),':',''),'0') ) from te2 b where b.ID = a.ID
and coalesce(replace(to_char(a.out_TIME,'hh:mi'),':',''),'0') > coalesce(replace(to_char(b.in_TIME,'hh:mi'),':',''),'0')
) yyy
from te2 a
)
where
(coalesce(replace(coalesce(replace(to_char(in_TIME,'hh:mi'),':',''),'0'),':',''),'0') between replace('07:00',':','') and replace('10:20',':','')
and coalesce(replace(xxx,':',''),'0') between replace('08:00',':','') and replace('11:30',':','')
)
or
(coalesce(replace(coalesce(replace(to_char(out_TIME,'hh:mi'),':',''),'0'),':',''),'0') between replace('08:00',':','') and replace('11:30',':','')
and coalesce(replace(yyy,':',''),'0') between replace('07:00',':','') and replace('10:20',':','')
)


"ID","IN_TIME","OUT_TIME"
"1","","2009-08-01 10:15:00"
"1","2009-08-01 09:30:00",""
"1","","2009-08-01 09:00:00"
"1","2009-08-01 08:30:00",""
"2","","2009-08-01 08:10:00"
"2","2009-08-01 07:40:00",""

-----------------------------------------------------------

No.2 回答者： naktak 回答日時： 2009/08/12 16:06

結構汚くなっちゃいましたけど、一応できました。

全角スペース入れてるので置換して下さい。
Oracle上で試したので微妙に文法誤ってるかもしれません。
8.1.9ならいけそうなことしかしてないので、微妙な文法違いは直して下さいね。

SELECT　id,
　　　　in_time,
　　　　out_time
FROM　　test A
WHERE　 A.in_time >= to_date('1899/12/30 7:00', 'yyyy/mm/dd hh24:mi:ss') AND
　　　　A.in_time <= to_date('1899/12/30 10:20', 'yyyy/mm/dd hh24:mi:ss') AND
　　　　EXISTS(SELECT 1 FROM test B
　　　　　　　 WHERE B.id = A.id AND
　　　　　　　　　　 B.out_time >= TO_DATE('1899/12/30 8:00', 'yyyy/mm/dd hh24:mi:ss') AND
　　　　　　　　　　 B.out_time <= TO_DATE('1899/12/30 11:30', 'yyyy/mm/dd hh24:mi:ss') AND
　　　　　　　　　　 B.out_time = (SELECT MIN(C.out_time) FROM test C
　　　　　　　　　　　　　　　　　 WHERE C.id = A.id AND
　　　　　　　　　　　　　　　　　　　　 C.out_time >= A.in_time)

)
UNION ALL
SELECT　id,
　　　　in_time,
　　　　out_time
FROM　　test A
WHERE　 A.out_time >= TO_DATE('1899/12/30 8:00', 'yyyy/mm/dd hh24:mi:ss') AND
　　　　A.out_time <= TO_DATE('1899/12/30 11:30', 'yyyy/mm/dd hh24:mi:ss') AND
　　　　EXISTS(SELECT 1 FROM test B
　　　　　　　 WHERE B.id = A.id AND
　　　　　　　　　　 B.in_time >= TO_DATE('1899/12/30 7:00', 'yyyy/mm/dd hh24:mi:ss') AND
　　　　　　　　　　 B.in_time <= TO_DATE('1899/12/30 10:20', 'yyyy/mm/dd hh24:mi:ss') AND
　　　　　　　　　　 B.in_time = (SELECT MAX(C.in_time) FROM test C
　　　　　　　　　　　　　　　　　 WHERE C.id = A.id AND
　　　　　　　　　　　　　　　　　　　　 C.in_time <= A.out_time)

)

これだとin_timeとout_timeがグループされて出てきますが、
in_time、out_timeがセットで交互に出てこないとダメでしょうか？

No.1 回答者： naktak 回答日時： 2009/08/12 11:34

> idごとにin_timeとout_timeがセット

何をもってそう判断すれば良いのでしょうか？

このレコードからすると、
　1　　　　　　　　　11:10
　1　　　09:30
がセットである可能性もあると思うのですが。

この回答への補足

説明不足でした、すみません。
in_timeのレコードから現在に向かって探し、
最初に見つかった同じidでout_timeが入力さ
れているレコードがセットになります。

補足日時：2009/08/12 12:21