首页 > 解决方案 > 如何在新列中输出每年的结果?

问题描述

此代码在一行中显示数据,但我需要在单独的列中显示每年的结果。你可以帮帮我吗?

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;

标签: sqlpostgresqlsubquery

解决方案


使用条件聚合:

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;

推荐阅读