admin

将一列优先于另一列

sql

DB提琴

CREATE TABLE logistics (
    id int primary key,
    campaign VARCHAR(255),
    quantity_offered VARCHAR(255),
    quantity_ordered VARCHAR(255), 
    quantity_delivered VARCHAR(255),
    quantity_recorded VARCHAR(255),
    quantity_completed VARCHAR(255)
);

INSERT INTO logistics
(id, campaign,
quantity_offered, quantity_ordered, quantity_delivered, 
quantity_recorded, quantity_completed
)
VALUES 
("1", "C001", "500", "450", "465", "462", "465"),
("2", "C002", "700", "570", NULL, NULL, NULL),
("3", "C003", "600", "610", "605", "602", NULL),
("4", "C004", "300", NULL, NULL, NULL, NULL),
("5", "C005", "400", "425", NULL, NULL, NULL),
("6", "C006", "900", "870", "868", NULL, NULL),
("7", "C007", "350", "360", "372", "375", "390"),
("8", "C008", "250", "290", NULL, NULL, NULL);

在上表中我campaigns与它们对应的有所不同quantities
quantities填写方式不同columns


现在,我想基于以下层次结构获取每种的 最新可用数量campaign

quantity_completed > quantity_recorded > quantity_delivered > quantity_ordered > quantity_offered

结果应如下所示:

Campaign      Quantity
C001            465
C002            570
C003            602
C004            300
C005            425
C006            870
C007            390
C008            290

我需要执行什么查询?


阅读 271

收藏
2021-07-01

共1个答案

admin

用途coalesce()

select campaign, coalesce(quantity_completed, quantity_recorded, quantity_delivered, quantity_ordered, quantity_offered) as quantity
from logistics;
2021-07-01