在SQL中配对发送和接收数据行 [英] Pairing Send and Receive Data Rows in SQL

问题描述

在SQL中将数据行配对以识别某项何时过期但不返回的问题。发送和接收条目位于不同的行上，并且两者之间存在数据关系，所以我认为这是可行的，我只是对解决方案感到困惑。模块和用户应将事务绑定在一起，但是另外还有一个批列，可用于进一步确保唯一事务

Having issues pairing data rows in SQL to identify when an item goes out, but does not return. The send and receive entries are on different rows, and there is a data relationship between the two, so I think this is doable, I'm just stumped as to the solution. The Module and User should tie together the transaction, however there is additionally a "batch" column that could be used to further ensure a unique transaction

示例表结构：

 MODULE         USER       EVENTDTTM                ACTION     Batch
 --------------------------------------------------------------------
 MODULE1        USERB      2016-01-09 13:00:00      SENT        001
 MODULE1        USERB      2016-01-09 13:01:00      RECEIVED    001
 MODULE2        USERA      2016-01-09 13:00:00      SENT        001
 MODULE2        USERA      2016-01-09 13:01:00      RECEIVED    001
 MODULE1        USERA      2016-01-09 13:03:00      SENT        002
 MODULE2        USERB      2016-01-09 13:04:00      SENT        002

我已经尝试过在表本身上进行联接，但是我只看到配对的数据（有大量重复的数据）...我找不到其中的情况已发送项目，但未找到配对的收据。

I've tried a to do a join on the table itself, but I'm only seeing the paired data (with crazy amounts of duplicates)...I cant find the scenarios where an item was sent, but no paired receipt was found.

推荐答案

select *
from
    (select * from T where Action = 'SENT') s
    left outer join
    (select * from T where Action = 'RECEIVED') r
        on r.Module and s.Module and r.User = s.User and r.Batch = s.Batch

从数量有限的示例数据中，您似乎可以通过拥有一个常见的模块，用户来唯一确定一个匹配项和批次。我不确定为什么您要在查询中重复输入。唯一的其他问题似乎是使用外部联接来保留尚没有接收的发送。

From the limited amount of sample data it appears that you can uniquely determine a match by having a common module, user and batch. I'm not sure why you came up with duplicates in your queries. The only other issue appears to be using an outer join to keep the "sends" that don't have a "receive" yet.

我认为您仍然想要所有内容结果。如果您只想要未配对的方案，则添加：

I think you still wanted everything in the result. If you did only want the unpaired scenarios then add:

where r.Module is null

这篇关于在SQL中配对发送和接收数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！