sql - 查找总和的最大值的子查询

Question

我有一个销售表，其中包含

date, store, weekly sales

我想找到每年销售额最高的商店。例如

year     store    total sales
2010      4        $2,000,000
2011      25       $1,000,000

我的子查询有效，但我找不到它的最大值

SELECT year, store, MAX(total_sales)
FROM (SELECT
     date_part('year', date) AS year, store, SUM(weekly_sales)
     FROM sales
     GROUP BY year, store) AS total_sales
GROUP BY year, store;

标签： sql

Answer 1

使用row_number()：

SELECT year, store, total_sales
FROM (SELECT date_part('year', date) AS year, store, SUM(weekly_sales) as total_sales,
             ROW_NUMBER() OVER (PARTITION BY date_part('year', date) ORDER BY SUM(weekly_sales) DESC) as seqnum
       FROM sales
       GROUP BY year, store
     ) ys
WHERE seqnum = 1;