本文介绍了来自两个表的SQL MIN值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有以下提到的数据.我正在寻找开始消息的最小值和成功消息的相应最小值.如果没有开始或成功消息,则应该显示为空.

I have below mentioned data. I am looking to get min of Start message and corresponding min of success message. If there is no start or success message present then it should show null.

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   abcd        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:29         5/14/2014 10:02:52           5/14/2014  abc start success 262 290
1234 5/14/2014 10:09:04         5/14/2014 10:09:32           5/14/2014  abc start success 165 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  abcd start success 250 null
5678    null                   5/19/2014 14:00:16            5/19/2014  pqr null  success null 400

我正在尝试将start_timestamp的最小值与下一个与id1和jobid对应的success_timestamp的最小值相结合.如果对于给定的id1和jobid,有启动消息列表,而没有成功消息,则它应该显示NULL,反之亦然.使用带有WITH子句的临时表进行了尝试,还使用了自连接方法.下面是我的查询,但是WITH子句查询返回表中全部数据的MIN.

I am trying to get Min of start_timestamp in combination with the very next Min of success_timestamp corresponding to id1 and jobid. If there is a list of start message and no success message for a given id1 and jobid, then it should show NULL and viceversa. Tried using Temporary table using WITH clause and also used self join method. Below is my query, But WITH clause query returns MIN of overall data in the table.

使用的查询:

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;

推荐答案

我能得到的最接近的结果是:您会注意到我的以下查询显示了一些无止境的开始..您没有解释如何处理这些情况.此外,我的查询还链接了您的不同起点和终点.你说

Closest I can get is this: You'll notice my below query is showing some starts without ends .. you don't explain what to do with those. Also, my query links different starts and ends from you. You said

以下是我做的,它所链接的是:"264 with 4280",而不是"165 with 4280".由于262与290配对,这意味着264现在是与成功MIN相匹配的MIN,即4280.如果您可以进一步阐明您的要求,我可以对查询进行调整.

Which I did below, and it links: "264 with 4280", not "165 with 4280" as you showed. Since 262 was paired up with 290, that means 264 is now the MIN to be matched with the MIN of success, 4280.If you can further clarify your requirements, I can tweak the query.

  with w_start as (
           select 1234 id1, to_date('05/14/2014 10:02:29','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 262 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 10:02:31','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 264 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 10:02:45','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 278 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 10:02:50','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 285 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 10:09:04','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 165 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 10:09:06','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 2167') msg from dual union all
           select 1234 id1, to_date('05/14/2014 10:09:16','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 2180') msg from dual union all
           select 1234 id1, to_date('05/14/2014 10:09:26','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 2190') msg from dual union all
           select 1234 id1, to_date('05/14/2014 11:45:11','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 8767') msg from dual union all
           select 1234 id1, to_date('05/14/2014 16:48:20','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 878 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 19:02:52','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abc' jobid, rtrim('start 687 ') msg from dual union all
           select 5678 id1, to_date('05/14/2014 22:02:52','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'pqr' jobid, rtrim('start 501 ') msg from dual union all
           select 5678 id1, to_date('05/14/2014 23:10:40','mm/dd/yyyy hh24:mi:ss') t_start, to_date('05/14/2014','mm-dd-yyyy') dt, 'abcd' jobid, rtrim('start 200 ') msg from dual
           ),
     w_success as (
           select 1234 id1, to_date('05/14/2014 10:02:52', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'abc' jobid, rtrim('successful 290  ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 10:09:32', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'abc' jobid, rtrim('successful 4280 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 11:45:15', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'abc' jobid, rtrim('successful 8774 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 11:45:18', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'abc' jobid, rtrim('successful 8777 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 11:45:19', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'abc' jobid, rtrim('successful 8778 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 11:45:25', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'abc' jobid, rtrim('successful 8784 ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 16:48:22', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'abc' jobid, rtrim('successful 880  ') msg from dual union all
           select 1234 id1, to_date('05/14/2014 19:03:00', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'abc' jobid, rtrim('successful 699  ') msg from dual union all
           select 5678 id1, to_date('05/14/2014 22:03:00', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/14/2014', 'mm/dd/yyyy') dt, 'pqr' jobid, rtrim('successful 250  ') msg from dual union all
           select 5678 id1, to_date('05/19/2014 14:00:16', 'mm/dd/yyyy hh24:mi:ss') t_end, to_date('05/19/2014', 'mm/dd/yyyy') dt, 'pqr' jobid, rtrim('successful 400  ') msg from dual
           ),
     w_sub1 as (
           select id1, jobid, dt, t_start, msg,
                    row_number() over (partition by id1, jobid
                                      order by t_start ) l1
             from w_start
           ),
     w_sub2 as (
           select id1, jobid, dt, t_end, msg,
                    row_number() over (partition by id1, jobid
                                      order by t_end ) l2
             from w_success
           )
  select nvl(a.id1,b.id1) id1, nvl(a.jobid, b.jobid) jobid, nvl(a.dt, b.dt) dt,
           t_start, t_end,
           a.msg, b.msg, l1, l2
    from w_sub1  a
        FULL OUTER JOIN w_sub2  b
           ON a.id1      = b.id1
          and a.jobid    = b.jobid
          and a.dt       = b.dt
          and a.l1       = b.l2
   order by 1, 4, 5, 2
  /

         ID1 JOBI DT                   T_START              T_END                MSG        MSG                     L1         L2
  ---------- ---- -------------------- -------------------- -------------------- ---------- --------------- ---------- ----------
        1234 abc  14-may-2014 00:00:00 14-may-2014 10:02:29 14-may-2014 10:02:52 start 262  successful 290           1          1
        1234 abc  14-may-2014 00:00:00 14-may-2014 10:02:31 14-may-2014 10:09:32 start 264  successful 4280          2          2
        1234 abc  14-may-2014 00:00:00 14-may-2014 10:02:45 14-may-2014 11:45:15 start 278  successful 8774          3          3
        1234 abc  14-may-2014 00:00:00 14-may-2014 10:02:50 14-may-2014 11:45:18 start 285  successful 8777          4          4
        1234 abc  14-may-2014 00:00:00 14-may-2014 10:09:04 14-may-2014 11:45:19 start 165  successful 8778          5          5
        1234 abc  14-may-2014 00:00:00 14-may-2014 10:09:06 14-may-2014 11:45:25 start 2167 successful 8784          6          6
        1234 abc  14-may-2014 00:00:00 14-may-2014 10:09:16 14-may-2014 16:48:22 start 2180 successful 880           7          7
        1234 abc  14-may-2014 00:00:00 14-may-2014 10:09:26 14-may-2014 19:03:00 start 2190 successful 699           8          8
        1234 abc  14-may-2014 00:00:00 14-may-2014 11:45:11                      start 8767                          9
        1234 abc  14-may-2014 00:00:00 14-may-2014 16:48:20                      start 878                          10
        1234 abc  14-may-2014 00:00:00 14-may-2014 19:02:52                      start 687                          11
        5678 pqr  14-may-2014 00:00:00 14-may-2014 22:02:52 14-may-2014 22:03:00 start 501  successful 250           1          1
        5678 abcd 14-may-2014 00:00:00 14-may-2014 23:10:40                      start 200                           1
        5678 pqr  19-may-2014 00:00:00                      19-may-2014 14:00:16            successful 400                      2

  14 rows selected.

这篇关于来自两个表的SQL MIN值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-23 02:30