如何在新列中输出每年的结果

Question

这段代码在一行中显示数据，但我需要在单独的列中显示每年的结果。你能帮我吗？

SELECT 
EXTRACT (YEAR FROM s.time_id)::int,
p.prod_subcategory,
sum(s.amount_sold)
FROM sales s
JOIN products p ON p.prod_id = s.prod_id
WHERE EXTRACT (YEAR FROM s.time_id) IN (1998,1999,2001)
GROUP BY EXTRACT (YEAR FROM s.time_id)::int, p.prod_subcategory
ORDER BY date_part;

Answer 1

使用条件聚合：

SELECT p.prod_subcategory,
SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 2000) as sales_2000,
SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 1999) as sales_1999,
SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 1998) as sales_1998
FROM sales s JOIN
products p
ON p.prod_id = s.prod_id
WHERE EXTRACT (YEAR FROM s.time_id) IN (1998,1999,2001)
GROUP BY p.prod_subcategory;

Answer 2

简单但不灵活的硬编码方式-使用filter子句：

SELECT
p.prod_subcategory,
sum(s.amount_sold) filter (where EXTRACT(YEAR FROM s.time_id)::int = 1998) as amount_1998,
sum(s.amount_sold) filter (where EXTRACT(YEAR FROM s.time_id)::int = 1999) as amount_1999,
sum(s.amount_sold) filter (where EXTRACT(YEAR FROM s.time_id)::int = 2001) as amount_2001
FROM sales s
JOIN products p ON p.prod_id = s.prod_id
WHERE EXTRACT(YEAR FROM s.time_id) IN (1998,1999,2001)
GROUP BY p.prod_subcategory
ORDER BY _whatever-you-need_;

更通用和优雅的方法是使用扩展tablefunc，函数crosstab。这是一个SO线程。