sql-server - 返回 id，其中与其他表上该 id 关联的值计数为 0

Question

我一直被困在一个相当简单的场景中，但即使在绞尽脑汁一段时间后，我仍然无法找到解决方案……这就是我所拥有的。

我有 2 个包含以下数据的表:

Trip (ID, Status)
30063   SUBMITTED
30066   SUBMITTED
30067   ASSIGNED
30068   SUBMITTED

和

AgentTripAssignment(TripId, AgentId, IsRejected)
30063   5   1
30063   2   0
30066   3   0
30066   4   0
30067   1   0
30067   2   0
30067   3   0

我想做的是:

• 从状态为 SUBMITTED 的行程表返回行程 ID，如果行程条目存在于其他表中，则在具有 1 个查询的 AgentTripAssignment 表中，IsRejected = 1 的计数为零(作为新查询.... 30066,30068 在给定的情况下)和

• 从行程表中返回行程 ID，其中状态为 SUBMITTED，并且条目存在于 AgentTripAssignment 表中，其 IsRejected = 1 计数在第二个查询的表中至少出现一次(因为代理被拒绝....30063给定的情况)

其他需要注意的一点是，行程状态会保持已提交状态，直到 3 个代理未分配给行程请求，此时状态更改为已分配，就像 30067 的情况一样。

任何帮助将不胜感激!

Answer 1

案例 1: 行程已提交，没有“拒绝”分配

这是在分配上使用左连接来处理给定行程没有分配的情况。如果拒绝状态的赋值数不为0，则排除。

SELECT t.ID
FROM Trip AS t
LEFT JOIN AgentTripAssignmentx AS at
    ON at.TripId = t.ID
WHERE t.Status = 'SUBMITTED'
GROUP BY t.ID
    HAVING COUNT(CASE WHEN at.IsRejected = 1 THEN 1 ELSE NULL END) = 0

案例 2:行程已提交，但已“拒绝”分配

与上面类似，但使用内部联接，并排除没有拒绝分配的行程。

SELECT t.ID
FROM Trip AS t
JOIN AgentTripAssignmentx AS at
    ON at.TripId = t.ID
WHERE t.Status = 'SUBMITTED'
GROUP BY t.ID
    HAVING COUNT(CASE WHEN at.IsRejected = 1 THEN 1 ELSE NULL END) > 0