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

我有以下两个表，您也可以在 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_Channel 和 Purchasing_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