sql - 在SQL UNION之后替换NULL列

我使用一个并集来组合两个select语句的结果。在一个语句中，我找到aidentity的sender，在另一个语句中，我找到aidentity的recipient。我遇到的问题是，我的行计数总是“应该”的两倍，因为收件人结果总是“空白”sender，发件人结果总是“空白”recipient。如何重新构造它，以便为每个同时填写了sender和recipient的事务返回一行？

(
  SELECT s.id, s.sender_id, '' AS sender, NOW() AS sender_create_date, s.recipient_id,
    MAX(c.identity) AS recipient, MAX(c.create_date) AS recipient_create_date
  FROM db.sales s
  LEFT JOIN db.customers c ON c.participant_id = s.recipient_id
  WHERE sender_id = $1
  OR recipient_id = $1
  GROUP BY s.id
)
UNION
(
  SELECT s.id, MAX(c.identity) AS sender, MAX(c.create_date) AS sender_create_date, s.recipient_id,
    '' AS recipient, NOW() AS recipient_create_date
  FROM db.sales s
  LEFT JOIN db.customers c ON c.participant_id = s.recipient_id
  WHERE sender_id = $1
  OR recipient_id = $1
  GROUP BY s.id
)

以下是一个事务的当前结果示例：

id | sender | sender_create_date | recipient | recipient_create_date
1  |  bob   |     11/1/2016      |           |    11/22/2016
1  |        |     11/22/2016     |    jill   |    11/5/2016

我的目标是：

id | sender | sender_create_date | recipient | recipient_create_date
1  |  bob   |     11/1/2016      |   jill    |    11/5/2016

最佳答案

我的目标可以通过两个连接来实现：

SELECT s.id, MAX(cs.identity) AS sender, MAX(cs.create_date) AS sender_create_date, MAX(cr.identity) AS recipient, MAX(cr.create_date) AS recipient_create_date
FROM db.sales s
LEFT JOIN db.customers cr ON cr.participant_id = s.recipient_id
LEFT JOIN db.customers cs ON cs.participant_id = s.sender_id
WHERE sender_id = $1
OR recipient_id = $1
GROUP BY s.id;