将新列添加到结果并合并表

Question

CREATE TABLE logistics (
id int primary key,
campaign VARCHAR(255),
event_type VARCHAR (255),
date_offered VARCHAR (255),
date_ordered DATE,
date_delivered DATE,
date_recorded DATE,
date_completed DATE,
quantity VARCHAR(255)
);

INSERT INTO logistics
(id, campaign, event_type, 
date_offered, date_ordered,
date_delivered, date_recorded, date_completed,
quantity
)
VALUES 
("1", "C001", "offered", "2019-04-10", NULL, NULL, NULL, NULL, "500"),
("2", "C001", "ordered", NULL, "2019-04-16", NULL, NULL, NULL, "450"),
("3", "C001", "stored", NULL, NULL, "2019-04-18", "2019-05-20", NULL, "465"),

("4", "C002", "offered", "2019-08-14", NULL, NULL, NULL, NULL, "700"),
("5", "C002", "ordered", NULL, "2019-09-04", NULL, NULL, NULL, "730"),
("6", "C002", "stored", NULL, NULL, "2019-09-15", "2019-09-18", "2019-09-19", "800");

我想运行一个查询：

a）sub_event_type在上面的表中添加一个新列，并且
b）coalesce该表所有date values都在一个列中，称为event_date

结果应如下所示：

campaign     event_type      sub_event_type     event_date         quantity
C001         offer           NULL               2019-04-10          500
C001         order           NULL               2019-04-16          450
C001         stored          delivered          2019-04-18          465
C001         stored          recorded           2019-05-20          465
C002         offer           NULL               2019-08-14          700
C002         order           NULL               2019-09-04          730
C002         stored          completed          2019-09-15          800
C002         stored          delivered          2019-09-18          800
C002         stored          recorded           2019-09-19          800

为了实现这一点，我尝试使用以下查询：

SELECT
id,
campaign,
event_type,

 (CASE
  WHEN event_type = "stored" AND date_delivered IS NOT NULL THEN "delivered"
  WHEN event_type = "stored" AND date_recorded IS NOT NULL THEN "recorded"
  WHEN event_type = "stored" AND date_completed IS NOT NULL THEN "completed"
  END) AS sub_event_type,

coalesce(date_offered, date_ordered, date_delivered, date_recorded, date_completed) as event_date,
quantity
FROM logistics;

但是，此查询仅显示sub_event_type delivered在结果中。
我假设此问题的原因在于，event_type stored所有date值都在一行中，在中，event_types
offered并且stored它们在两个单独的行中。

我必须如何html" target="_blank">修改查询才能获得预期的结果？

Answer 1

我认为您需要union all：

select id, campaign, event_type, 'delivered' as sub_event_type, date_delivered
from logistics
where event_type = 'stored' and date_delivered is not null
union all
select id, campaign, event_type, 'recorded' as sub_event_type, date_recorded
from logistics
where event_type = 'stored' and date_recorded is not null
union all
select id, campaign, event_type, 'completed' as sub_event_type, date_completed
from logistics
where event_type = 'stored' and date_completed is not null
union all
select id, campaign, event_type, null as sub_event_type, date_offered
from logistics
where event_type = 'offered' 
union all
select id, campaign, event_type, null as sub_event_type, date_ordered
from logistics
where event_type = 'ordered' ;