如何按组按2表选择甲骨文？

Question

我有两张桌子: CHAT_INCOMING，CHAT_OUTGOING。我想通过加入id和按时间排序从两个表中选择数据。例如，输出：

|ADD_TIME           |CHAT_ID|CHAT_INCOMING|CHAT_OUTGOING       |
|2019-01-01 10:00:01|1111   |Hello world  |                    |      
|2019-01-01 10:00:20|1111   |             | Reply: hello       |
|2019-01-01 10:00:23|1111   |             | Reply: How are you?|
|2019-01-01 10:00:25|1111   |I am fine.   |                    |
|2019-01-01 11:05:21|7585   |Where are you|                    |
|2019-01-01 11:06:01|7585   |             |I am at school.     |    



SELECT ADD_TIME, A.CHAT_ID, A.CHAT AS CHAT_INCOMING,
B.CHAT AS CHAT_OUTGOING
   from
 (SELECT to_char(ADD_TIME,'yyyy-MM-dd hh24:mi:ss') ADD_TIME, CHAT_ID, CHAT      
        FROM CHAT_INCOMING
                 WHERE to_char(ADD_TIME,'yyyy-MM-dd') >= :from_time
         AND to_char(ADD_TIME,'yyyy-MM-dd') <= :to_time
         AND (CHAT_ID = :i_CHAT_ID or :i_CHAT_ID = N'-')
        ) A,                       
           
           (SELECT to_char(SENT_ADD_TIME,'yyyy-MM-dd hh24:mi:ss') AS ADD_TIME, CHAT_ID, CHAT
        FROM CHAT_OUTGOING
 
              WHERE to_char(SENT_ADD_TIME,'yyyy-MM-dd') >= :from_time
         AND to_char(SENT_ADD_TIME,'yyyy-MM-dd') <= :to_time
         AND (CHAT_ID  LIKE :i_CHAT_ID || '%')
       ) B
  ORDER BY ADD_TIME ASC

我创建了这个查询，但是它忽略了SENT_ADD_TIME，CHAT_OUTGOING是复制的。输出如下所示

|ADD_TIME           |CHAT_ID|CHAT_INCOMING|CHAT_OUTGOING       |
|2019-01-01 10:00:01|1111   |Hello world  |Reply: hello        |      
|2019-01-01 10:00:20|1111   |I am fine.   |Reply: hello        |
|2019-01-01 10:00:23|1111   |Where are you|Reply: hello        |

我需要使用union all或group by还是其他什么的吗？

Answer 1

这里有一个选择。请参阅代码中的注释。

SQL>  with
  2  -- sample data
  3  chat_incoming (add_time, chat_id, chat) as
  4    (select to_date('2019-01-01 10:00:01', 'yyyy-mm-dd hh24:mi:ss'), 1111, 'Hello world'    from dual union all
  5     select to_date('2019-01-01 10:00:25', 'yyyy-mm-dd hh24:mi:ss'), 1111, 'I am fine'      from dual union all
  6     select to_date('2019-01-01 11:05:21', 'yyyy-mm-dd hh24:mi:ss'), 7585, 'Where are you'  from dual
  7    ),
  8  chat_outgoing (add_time, chat_id, chat) as
  9    (select to_date('2019-01-01 10:00:20', 'yyyy-mm-dd hh24:mi:ss'), 1111, 'Hello'          from dual union all
 10     select to_date('2019-01-01 10:00:23', 'yyyy-mm-dd hh24:mi:ss'), 1111, 'How are you?'   from dual union all
 11     select to_date('2019-01-01 11:06:01', 'yyyy-mm-dd hh24:mi:ss'), 7585, 'I am at school' from dual
 12    ),
 13  unioned as
 14    -- create a "view" which contains both incoming and outgoing data
 15    (select 'I' what, i.add_time, i.chat_id, i.chat
 16     from chat_incoming i
 17     union
 18     select 'O' what, o.add_time, o.chat_id, o.chat
 19     from chat_outgoing o
 20    )
 21  -- final query
 22  select add_time, chat_id,
 23    max(case when what = 'I' then chat end) chat_incoming,
 24    max(case when what = 'O' then chat end) chat_outgoing
 25  from unioned
 26  group by add_time, chat_id
 27  order by chat_id, add_time;

ADD_TIME               CHAT_ID CHAT_INCOMING  CHAT_OUTGOING
------------------- ---------- -------------- --------------
01.01.2019 10:00:01       1111 Hello world
01.01.2019 10:00:20       1111                Hello
01.01.2019 10:00:23       1111                How are you?
01.01.2019 10:00:25       1111 I am fine
01.01.2019 11:05:21       7585 Where are you
01.01.2019 11:06:01       7585                I am at school

6 rows selected.

SQL>

如何使用WHERE子句？

SQL> with
  2  unioned as
  3    -- create a "view" which contains both incoming and outgoing data
  4    (select 'I' what, i.add_time, i.chat_id, i.chat
  5     from chat_incoming i
  6     union
  7     select 'O' what, o.add_time, o.chat_id, o.chat
  8     from chat_outgoing o
  9    )
 10  -- final query
 11  select add_time, chat_id,
 12    max(case when what = 'I' then chat end) chat_incoming,
 13    max(case when what = 'O' then chat end) chat_outgoing
 14  from unioned
 15  where chat_id like &par_chat ||'%'
 16    and add_time between to_date('&par_date_from', 'dd.mm.yyyy')
 17                     and to_date('&par_date_to'  , 'dd.mm.yyyy')
 18  group by add_time, chat_id
 19  order by chat_id, add_time;
Enter value for par_chat: 1111
Enter value for par_date_from: 01.01.2019
Enter value for par_date_to: 02.01.2019

ADD_TIME               CHAT_ID CHAT_INCOMING  CHAT_OUTGOING
------------------- ---------- -------------- --------------
01.01.2019 10:00:01       1111 Hello world
01.01.2019 10:00:20       1111                Hello
01.01.2019 10:00:23       1111                How are you?
01.01.2019 10:00:25       1111 I am fine

SQL>