gpt4 book ai didi

mysql - 将文本值与 LEFT JOIN 合并在一列中,并用预定义的措辞替换 NULL

转载 作者:行者123 更新时间:2023-12-03 08:34:38 26 4
gpt4 key购买 nike

我有以下两个表,您也可以在 sql fiddle here 中找到它们。 :

CREATE TABLE Sales (
Sales_Date DATE,
Product TEXT,
Sales_Channel TEXT,
Sales_Quantity VARCHAR(255)
);

INSERT INTO Sales
(Sales_Date, Product, Sales_Channel, Sales_Quantity)
VALUES
("2017-05-23", "Product A", "Online", "400"),
("2018-09-10", "Product A", "Store", "200"),
("2018-12-14", "Product B", "Store", "600"),
("2019-01-03", "Product B", "Store", "700"),
("2019-02-15", "Product B", "Online", "650"),
("2019-03-20", "Product A", "Online", "380"),
("2019-08-25", "Product C", "TradeFair", "120"),
("2019-09-16", "Product C", "Online", "470"),
("2019-09-16", "Product A", "Store", "920"),
("2019-10-20", "Product B", "TradeFair", "860"),
("2020-01-03", "Product B", "TradeFair", "610");


CREATE TABLE Purchasing (
Purchasing_Date VARCHAR(255),
Product TEXT,
Purchasing_Channel TEXT,
Purchasing_Quantity VARCHAR(255)
);

INSERT INTO Purchasing
(Purchasing_Date, Product, Purchasing_Channel, Purchasing_Quantity)
VALUES
("2017-01-10", "Product A", "Local_Supplier", "1000"),
("2017-01-16", "Product A", "Local_Supplier", "3000"),
("2018-05-14", "Product C", "Foreign_Import", "1800"),
("2019-04-16", "Product C", "Foreign_Import", "2300");

我使用以下查询将 Sales_ChannelPurchasing_Channel 连接到一列中:

SELECT
s.Sales_Date,
s.Product,
concat(s.Sales_Channel, '_', p.Purchasing_Channel) as Chanel,
s.Sales_Quantity
FROM Sales s
LEFT JOIN Purchasing p ON p.Product = s.Product

到目前为止,所有这些都运行良好。


正如您在上表中所见,产品 B 没有 Purchasing_Channel
因此,产品 B 被列为 (NULL)

但是,我希望将 (NULL) 替换为 Sales_Channel + NoPurchasingChannel 措辞。
最后的结果应该是这样的:

Sales_Date   Product      Channel                             Sales_Quantity
2017-05-23 Product A Online_Local_Supplier 400
2018-09-10 Product A Store_Local_Supplier 200
2018-12-14 Product B Store_NoPurchasingChannel 600
2019-01-03 Product B Store_NoPurchasingChannel 700
2019-02-12 Product B Store_NoPurchasingChannel 650
2019-03-20 Product A Online_Local_Supplier 380
: : : :
: : : :
: : : :

我需要在查询中更改哪些内容才能使其正常工作?

最佳答案

您可以使用IFNULL()用其他值替换 NULL 值,例如:

SELECT ...
...
concat(s.Sales_Channel, '_', IFNULL(p.Purchasing_Channel, 'NoPurchasingChannel')) as Chanel,
...

working fiddle

关于mysql - 将文本值与 LEFT JOIN 合并在一列中,并用预定义的措辞替换 NULL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/60096428/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com