我有以下提到的数据。我希望获得最大的启动消息以及相应的最小或成功消息。
开始消息表
ID1 Timestamp_start_msg_recieved date jobid message time in seconds 1234 5/14/2014 10:02:29 5/14/2014 abc start 262 1234 5/14/2014 10:02:31 5/14/2014 abc start 264 1234 5/14/2014 10:02:45 5/14/2014 abc start 278 1234 5/14/2014 10:02:50 5/14/2014 abc start 285 1234 5/14/2014 10:09:04 5/14/2014 abc start 165 1234 5/14/2014 10:09:06 5/14/2014 abc start 2167 1234 5/14/2014 10:09:16 5/14/2014 abc start 2180 1234 5/14/2014 10:09:26 5/14/2014 abc start 2190 1234 5/14/2014 11:45:11 5/14/2014 abc start 8767 1234 5/14/2014 16:48:20 5/14/2014 abc start 878 1234 5/14/2014 19:02:52 5/14/2014 abc start 687 5678 5/14/2014 22:02:52 5/14/2014 pqr start 501 5678 5/14/2014 23:10:40 5/14/2014 pqr start 200
成功消息表
ID1 Timestamp_success_msg_recieved date jobid message time in seconds 1234 5/14/2014 10:02:52 5/14/2014 abc successful 290 1234 5/14/2014 10:09:32 5/14/2014 abc successful 4280 1234 5/14/2014 11:45:15 5/14/2014 abc successful 8774 1234 5/14/2014 11:45:18 5/14/2014 abc successful 8777 1234 5/14/2014 11:45:19 5/14/2014 abc successful 8778 1234 5/14/2014 11:45:25 5/14/2014 abc successful 8784 1234 5/14/2014 16:48:22 5/14/2014 abc successful 880 1234 5/14/2014 19:03:00 5/14/2014 abc successful 699 5678 5/14/2014 22:03:00 5/14/2014 pqr successful 250 5678 5/19/2014 14:00:16 5/19/2014 pqr successful 400
ID1 IMESTAMP_for_start_message TIMESTAMP_for_success_message Date Jobid msg msg start_secs success_secs 1234 5/14/2014 10:02:50 5/14/2014 10:02:52 5/14/2014 abc start success 262 290 1234 5/14/2014 10:09:26 5/14/2014 10:09:32 5/14/2014 abc start success 2190 4280 1234 5/14/2014 11:45:11 5/14/2014 11:45:25 5/14/2014 abc start success 8767 8784 1234 5/14/2014 16:48:20 5/14/2014 16:48:22 5/14/2014 abc start success 878 880 1234 5/14/2014 19:02:52 5/14/2014 19:03:00 5/14/2014 abc start success 687 699 5678 5/14/2014 22:02:52 5/14/2014 22:03:00 5/14/2014 pqr start success 501 699 5678 5/14/2014 23:10:40 null 5/14/2014 pqr start success 250 null 5678 null 5/19/2014 14:00:16 5/19/2014 pqr null success null 400
我正在寻找启动消息和start_secs的最大值，以与成功消息和success_secs的最小值配对。使用带有WITH子句的临时表进行了尝试，还使用了自连接方法。下面是我的查询，但是WITH子句查询返回表中全部数据的MIN。
使用的查询：
WITH DATA AS (SELECT MIN(smt.column13) timestamp_for_success_message FROM success_table1 smt, start_table2 b WHERE (SMT.id1 = b.id1) AND (SMT.jobid = b.jobid) AND (SMT.timestamp_for_success_message_recieved >= b.timestamp_for_start_message_recieved) ) SELECT distinct a.timestamp_for_success_message_recieved, b.timestamp_for_start_message_recieved, b.id1, b.jobid FROM data a, start_table2 b order by b.timestamp_start_message_recieved, a.timestamp_for_success_message_recieved, b.jobid, b.id1;
对于示例数据，以下内容也适用
WITH RawData AS ( SELECT id1 , jobID , message , TIMESTAMP_for_start_message timeStamp , time_in_seconds FROM StartMessageTable UNION ALL SELECT id1 , jobID , message , TIMESTAMP_success_msg_received , time_in_seconds FROM SuccessMessageTable ORDER BY id1, jobID, 4 ), Detail AS ( SELECT id1 , jobID , message message1 , LEAD (message, 1) OVER (ORDER BY id1, jobID, timeStamp) message2 , timeStamp timeStamp1 , LEAD (timeStamp, 1) OVER (ORDER BY id1, jobID, timeStamp) timeStamp2 , time_in_seconds secs1 , LEAD (time_in_seconds, 1) OVER (ORDER BY id1, jobID, timeStamp) secs2 FROM RawData ) SELECT id1 , timeStamp1 TIMESTAMP_for_start_message , timeStamp2 TIMESTAMP_for_success_message , jobID , 'start' msgStart , 'success' msgSuccess , secs1 start_secs , secs2 success_secs FROM Detail WHERE (message1 = 'start' AND message2 = 'successful') OR message2 IS NULL ORDER BY timeStamp1 ;
对于“ start_secs”列中的第一个和最后一个值，有相同的警告。 SQL小提琴
