将一列置于另一列之上 [英] Prioritize one column over another
本文介绍了将一列置于另一列之上的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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屋!
查看全文