选择分组查询

Question

我有下面的查询，它为每个“SENDER_REFERENCE_NB”返回两项，一个MESSAGE_STATUS是“(Receive)”，另一个是MESSAGE_STATUS是“Matched”。两者都包含一个CREATE_TM

select MESSAGE_ID, CREATE_TM, MESSAGE_STATUS
from TMS_MESSAGE_AUDIT_LOG
where message_id in (select message_id from tms_message
                     WHERE SENDER_REFERENCE_NB like '150703%')
  and MESSAGE_STATUS in ('(Receive)', 'Matched');

我想要实现的是，对于每个SENDER_REFERENCE_NB，我看到与此条目关联的(‘接收’)状态和CREATE_TM，以及关联的'Matched‘状态和CREATE_TM，所有这些都在一行上。

Answer 1

做一些假设，这是我的答案：

Select L1.MESSAGE_ID, L1.CREATE_TM, L1.MESSAGE_STATUS, 
       L2.MESSAGE_ID, L2.CREATE_TM, L2.MESSAGE_STATUS
from TMS_MESSAGE_AUDIT_LOG AS L1
INNER JOIN TMS_MESSAGE_AUDIT_LOG AS L2
ON L1.MESSAGE_STATUS = '(Receive)'
AND L1.SENDER_REFERENCE_NB like '150703%'
AND L2.SENDER_REFERENCE_NB like '150703%'
AND L2.MESSAGE_STATUS = 'Matched'

您可以在in this fiddle操作中看到它。