向结果和合并表添加新列 [英] Add new column to result and coalesce table

问题描述

数据库小提琴

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");

我想运行一个查询:

I want to run a query that:

a) 在上表中添加一个新列 sub_event_type 和
b) coalesce 表格，所以所有 date values 都在一个名为 event_date

a) adds a new column sub_event_type to the above table and
b) coalesce the table so all date values are in one column called 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

为了实现这一点，我尝试使用此查询:

In order to achieve this I tried to go with this query:

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 它们在两个单独的行中.

However, this query only displays sub_event_type delivered in the result.
I assume the reason for this issue ist that in the event_type stored all date values are in one row and in the event_types offered and stored they are in two seperate rows.

我必须如何修改查询才能获得预期结果?

How do I have to modify my query to get the expected result?

推荐答案

我认为你需要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' ;

这篇关于向结果和合并表添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！