假设有这么个表
我想把每一年的销售额(Amount)以列的形式展示,每行代表一个产品(product)如图
答案:
建表
CREATE TABLE Sales (
Product VARCHAR(50) NOT NULL,
Year INT NOT NULL,
Amount INT NOT NULL
);
INSERT INTO Sales (Product, Year, Amount)
VALUES ('ProductA', 2020, 1000),
('ProductA', 2021, 1500),
('ProductB', 2020, 1200),
('ProductB', 2021, 1800),
('ProductC', 2020, 800),
('ProductC', 2021, 1000);
查询:
SELECT
Product,
SUM(CASE WHEN Year = 2020 THEN Amount ELSE 0 END) AS '2020',
SUM(CASE WHEN Year = 2021 THEN Amount ELSE 0 END) AS '2021'
FROM
Sales
GROUP BY
Product;
解释一下:CASE WHEN Year = 2020 THEN Amount ELSE 0 END 用于判断,如果年份是2020,则选择Amount的值,否则选择0。而 sum( case when ) + group by 则是处理行转列问题的常用套路,你学会了嘛。哈哈,如果觉得有收获,欢迎关注。