在 WHERE 子句中使用 OR 条件作为结果中的列名 [英] Use OR conditions in WHERE clause as column names in result

问题描述

SQL 小提琴:

CREATE TABLE Sales (
    Product_ID VARCHAR(255),
    Country VARCHAR(255),
    Sales_Volume VARCHAR(255)
);

INSERT INTO Sales
(Product_ID, Country, Sales_Volume)
VALUES 
("P001", "US", "500"),
("P001", "US", "100"),
("P003", "US", "800"),
("P002", "DE", "300"),
("P001", "DE", "700"),
("P002", "NL", "200"),
("P002", "NL", "400");

在表格中，我有不同Countries 的Sales.
现在，我想总结每个 Country 的 Sales.
预期结果应如下所示:

In the table I have the the Sales in different Countries.
Now, I want to sum up the Sales per Country.
The expected result should look like this:

                US             DE 
P001           600            700
P002           NULL           300
P003           800            NULL

到目前为止，我有以下查询:

So far I have the following query:

SELECT
Product_ID,
Country,
SUM(Sales_Volume)
FROM Sales
WHERE 
Country = "US" 
OR Country ="DE"
GROUP BY 1,2;

此查询基本上有效，但不是将 Country 显示为 column name，而是将国家/地区显示为 value.

This query basically works but instead of displaying the Country as column name it displays the country as value.

我需要在我的 SQL 中更改什么才能获得我需要的结果?

What do I need to change in my SQL to get the result I need?

推荐答案

你可以使用条件聚合:

SELECT Product_ID,
       SUM(CASE WHEN Country = 'US' THEN Sales_Volume ELSE 0 END) as US,
       SUM(CASE WHEN Country = 'DE' THEN Sales_Volume ELSE 0 END) as DE
FROM Sales
GROUP BY Product_ID;

这篇关于在 WHERE 子句中使用 OR 条件作为结果中的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！