时间戳

首页 > 解决方案 > 如何在汇总后排序而不是拆分分组？

sql - 如何在汇总后排序而不是拆分分组？

问题描述

我使用汇总，我想按最佳年份排序（按利润而不是按字母顺序排列）而不是拆分年份组。
每年按最佳产品排序（按利润不按字母顺序排列）而不是拆分等。

我想要这样的东西：

表格1

但我只有这个：

DROP TABLE IF EXISTS abc;
CREATE TABLE abc(year  text,
                      country text, product text, profit integer);

  INSERT INTO abc VALUES ('2016', 'USA', 'Phone', 3000);
  INSERT INTO abc VALUES ('2016', 'USA', 'TV', 1000);
  INSERT INTO abc VALUES ('2016', 'UK', 'TV', 2000);
  INSERT INTO abc VALUES ('2016', 'UK', 'Phone', 1000);
  INSERT INTO abc VALUES ('2015', 'USA', 'TV', 1500);
  INSERT INTO abc VALUES ('2015', 'USA', 'Phone', 1000);
  INSERT INTO abc VALUES ('2015', 'UK', 'Phone', 900);
  INSERT INTO abc VALUES ('2015', 'UK', 'TV', 600);
  INSERT INTO abc VALUES ('2014', 'USA', 'Phone', 1500);
  INSERT INTO abc VALUES ('2014', 'USA', 'TV', 1000);
  INSERT INTO abc VALUES ('2014', 'UK', 'Phone', 1500);
  INSERT INTO abc VALUES ('2014', 'UK', 'TV', 2000);

with data as
(
SELECT year, country, product, profit from abc
  )
 select l.year as year, l.country as country, l.product as product,
  sum(l.profit) as profit
 from data l 
group by rollup(year, country, product)
ORDER BY 
CASE WHEN year IS NULL THEN 1 ELSE 0 END, year

当我按总和（l.profit）订购时，它会分裂几年。

标签： sqlpostgresqlrollup

解决方案

在 order by 子句中添加更多内容：

DROP TABLE IF EXISTS abc;
CREATE TABLE abc(year  text,
                  country text, product text, profit integer);

  INSERT INTO abc VALUES ('2016', 'USA', 'Phone', 3000);
  INSERT INTO abc VALUES ('2016', 'USA', 'TV', 1000);
  INSERT INTO abc VALUES ('2016', 'UK', 'TV', 2000);
  INSERT INTO abc VALUES ('2016', 'UK', 'Phone', 1000);
  INSERT INTO abc VALUES ('2015', 'USA', 'TV', 1500);
  INSERT INTO abc VALUES ('2015', 'USA', 'Phone', 1000);
  INSERT INTO abc VALUES ('2015', 'UK', 'Phone', 900);
  INSERT INTO abc VALUES ('2015', 'UK', 'TV', 600);
  INSERT INTO abc VALUES ('2014', 'USA', 'Phone', 1500);
  INSERT INTO abc VALUES ('2014', 'USA', 'TV', 1000);
  INSERT INTO abc VALUES ('2014', 'UK', 'Phone', 1500);
  INSERT INTO abc VALUES ('2014', 'UK', 'TV', 2000);

with data as
(
SELECT year, country, product, profit from abc
  )
 select l.year as year, l.country as country, l.product as product,
  sum(l.profit) as profit
 from data l 
group by rollup(year, country, product)
ORDER BY 
CASE WHEN year IS NULL THEN 1 ELSE 0 END,
year,
case when product is NULL THEN 1 else 0 end,
profit desc

推荐阅读