联合两个表中的所有客户，并从第二个表分配日期值，以防第一个表中不存在客户

Question

DB Fiddle

CREATE TABLE sales 
(
id SERIAL PRIMARY KEY,
last_order DATE,
customer VARCHAR(255)
);
INSERT INTO sales (last_order, customer)
VALUES 
('2020-09-10', 'user_01'),
('2020-10-15', 'user_02'),
('2020-11-26', 'user_03');

CREATE TABLE customers 
(
id SERIAL PRIMARY KEY,
first_order DATE,
customer VARCHAR(255)
);
INSERT INTO customers (first_order, customer)
VALUES 
('2020-08-10', 'user_01'),
('2020-09-15', 'user_02'),
('2020-10-17', 'user_03'),
('2020-05-03', 'user_04'),
('2020-04-12', 'user_05');

预期结果：

customer   |   used_date    |
-----------|----------------|------
user_01    |   2020-09-10   |
user_02    |   2020-10-15   |
user_03    |   2020-11-26   |
user_04    |   2020-05-03   |
user_05    |   2020-04-12   |

我有两个表，我想查询两个表中的所有客户。

此外，如果客户不在表sales中，我想将表customers中的first_order应用为used_date。

到目前为止，我提出了这个问题：

SELECT
t1.customer,
(CASE WHEN t1.last_order IS NULL THEN t1.first_order ELSE t1.last_order END) AS used_date
FROM
(SELECT
s.customer,
s.last_order AS last_order,
NULL::date AS first_order
FROM 
sales s
GROUP BY 
1, 2, 3
UNION ALL

SELECT
c.customer,
NULL::date AS last_order,
c.first_order AS first_order
FROM 
customers c
GROUP BY 
1, 2, 3) t1
GROUP BY 
1, 2
ORDER BY 
1;

我需要如何修改CASE WHEN语句才能得到预期的结果？

Answer 1

这看起来像left join:

select c.*, 
coalesce(s.last_order, c.first_order) as used_date
from customers c left join
sales s
on c.customer = s.customer;