将一列优先于另一列

  • Z时代
  • 2024-01-10
  • 分类：问答

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

我需要执行什么查询？

回答：

用途coalesce()：

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

以上是 将一列优先于另一列 的全部内容， 来源链接： utcz.com/qa/408247.html

回到顶部