将一列置于另一列之上 [英] Prioritize one column over another

问题描述

DB-Fiddle

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 填入不同的列.

In the table above I have different campaigns with their corresponding quantities.
The quantities are filled in different columns.

现在，我想根据以下层次结构获取每个广告系列的最新可用数量:

Now, I want to get the latest available quantity for each campaign based on the following hierarchy:

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

我需要什么查询来实现这一点?

What query do I need to achieve this?

推荐答案

使用 coalesce():

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

这篇关于将一列置于另一列之上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！