mysql - 合并列并替换 NULL 值

Question

DB-Fiddle

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

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

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

我想运行 coalesce 的查询这样的值:

a) 所有 date values在名为 event_date 的一栏中和
b) 如果没有 quantity对于 event_type stored (如您所见 C001 ) quantity的 event_type order应该使用。

结果应该是这样的:

campaign     event_type       event_date         quantity
C001         offered          2019-04-10          500
C001         ordered          2019-04-16          450
C001         stored           2019-04-18          450
C002         offered          2019-08-14          700
C002         ordered          2019-09-04          730
C002         stored           2019-09-15          800

我试着这样做:

SELECT id,
       campaign,
       event_type,
       coalesce(date_offered, date_ordered, date_delivered) as event_date,
       quantity
  FROM logistics;

通过此查询，我接近预期结果，但对于 C001有一个NULL对于 C001在event_type stored .

我必须如何修改我的查询才能获得 quantity的 event_type ordered对于 event_type stored对于 C001 ？

Answer 1

您可以使用相关子查询:

SELECT l.id, l.campaign, l.event_type,
       coalesce(l.date_offered, l.date_ordered, l.date_delivered) as event_date,
       coalesce(l.quantity,
                      (SELECT MAX(l1.quantity)
                       FROM logistics l1
                       WHERE l1.event_type = 'ordered' AND l.campaign = l1.campaign
                      )
               ) as new_quantity
FROM logistics l;