sql query: For each record where is_queue_empty=1 and queue_name is empty get immediate next record by timestamp where …
How can we construct sql query with following constraint.
For each record where is_queue_empty=1 and queue_name is empty get im开发者_StackOverflowmediate next record by timestamp where is_queue_empty=0 and queue_name can or cannot be empty for same session-id and request-id.
Table has following columns:
session_id,request_id,queue_name ,is_queue_empty,timestamp,queue_tag,tab_name.
What I have do far is this which is incorrect:
SELECT x.tab_name,
x.is_queue_empty,
x.SESSION_ID,
x.request_ID,
x.TO_CHAR(DATETIME, 'YYYY/MM/DD HH24:MI:SS') timestamp,
y.tab_name,y.queue_name,y.is_queue_empty
FROM queue_data AS x
WHERE
timesttamp < TO_DATE('2011/02/30')
AND timestamp >= TO_DATE('2011/01/01')
AND is_queue_empty=1
AND timestamp < (select TO_CHAR(timestamp, 'YYYY/MM/DD HH24:MI:SS') as timestamp from queue_data as Y where x.session_id = y.session_id and x.request_id=y.request_id and y.is_queue_empty=0 order by y.timestamp asc limit 1 )
select a.session_id,a.request_id,a.timestamp,a.queue_tag,
b.*
from
(
select session_id,request_id,timestamp,queue_tag,
(select min(b.timestamp)
from tbl b
where a.session_id=b.session_id
and a.request_id=b.request_id
and b.timestamp > a.timestamp
and b.is_queue_empty=0) nextrec
from tbl a
where is_queue_empty=1 and nullif(queue_name,'') is null
) a
left join tbl b on a.session_id=b.session_id
and a.request_id=b.request_id
and a.nextrec = b.timestamp
Note:
tbl
is the name of the table- Assuming timestamp is unique within a session_id,request_id combo
精彩评论