时间戳

首页 > 解决方案 > 如何获取仅按一列分组的小计

sql - 如何获取仅按一列分组的小计

问题描述

如果我有一张这样的桌子：

LOCATION         CODE       COMPANY            PRICE
---------        ----       -------       ----------
Sydney       |    A    |    ACME     |          1200
Brisbane     |    C    |    WONKA    |          3000
Melbourne    |    B    |    ACME     |           500
Sydney       |    A    |    ACME     |           100
Brisbane     |    A    |    WONKA    |          1000
Melbourne    |    C    |    ACME     |          7000
Sydney       |    A    |    ACME     |           800
Brisbane     |    B    |    WONKA    |           900
Melbourne    |    C    |    WONKA    |           200
Sydney       |    C    |    ACME     |           400
Brisbane     |    B    |    ACME     |          1200
Melbourne    |    D    |    WONKA    |           600
Sydney       |    A    |    ACME     |          3000
Brisbane     |    A    |    WONKA    |           400
Melbourne    |    C    |    WONKA    |          1400
Sydney       |    A    |    ACME     |          1600
Brisbane     |    A    |    WONKA    |           700
Melbourne    |    A    |    ACME     |           800

我想获得类似的东西：

LOCATION         CODE       COMPANY         SUM(PRICE)
---------        ----       -----           ----------
Brisbane     |    A    |    WONKA    |          2100
Brisbane     |    B    |    ACME     |          1200
Brisbane     |    B    |    WONKA    |           900
Brisbane     |    C    |    WONKA    |          3000
Brisbane     |         |             |          7200 
Melbourne    |    A    |    ACME     |           800
Melbourne    |    B    |    ACME     |           500
Melbourne    |    C    |    ACME     |          7000
Melbourne    |    C    |    WONKA    |          1600
Melbourne    |    D    |    WONKA    |           600
Melbourne    |         |             |         10500
Sydney       |    A    |    ACME     |          6700
Sydney       |    C    |    ACME     |           400
Sydney       |         |             |          7100

这是按位置、代码和公司分组的价格总计，带有价格小计行（仅按位置分组）。

第一部分可以通过以下查询来实现：

with sample_tab as
(
select 'Sydney' location,'A' code, 'ACME' company, 1200 price from dual
union all
select 'Brisbane' location,'C' code, 'WONKA' company, 3000 price from dual
union all
select 'Melbourne' location,'B' code, 'ACME' company, 500 price from dual
union all
select 'Sydney' location,'A' code, 'ACME' company, 100 price from dual
union all
select 'Brisbane' location,'A' code, 'WONKA' company, 1000 price from dual
union all
select 'Melbourne' location,'C' code, 'ACME' company, 7000 price from dual
union all
select 'Sydney' location,'A' code, 'ACME' company, 800 price from dual
union all
select 'Brisbane' location,'B' code, 'WONKA' company, 900 price from dual
union all
select 'Melbourne' location,'C' code, 'WONKA' company, 200 price from dual
union all
select 'Sydney' location,'C' code, 'ACME' company, 400 price from dual
union all
select 'Brisbane' location,'B' code, 'ACME' company, 1200 price from dual
union all
select 'Melbourne' location,'D' code, 'WONKA' company, 600 price from dual
union all
select 'Sydney' location,'A' code, 'ACME' company, 3000 price from dual
union all
select 'Brisbane' location,'A' code, 'WONKA' company, 400 price from dual
union all
select 'Melbourne' location,'C' code, 'WONKA' company, 1400 price from dual
union all
select 'Sydney' location,'A' code, 'ACME' company, 1600 price from dual
union all
select 'Brisbane' location,'A' code, 'WONKA' company, 700 price from dual
union all
select 'Melbourne' location,'A' code, 'ACME' company, 800 price from dual
)
select location,code,company,sum(price)
from sample_tab
group by location,code,company
order by location;

但不确定如何按位置获取带有小计的行。这可以使用汇总分析函数或其他函数来实现吗？

标签： sqloracleplsql

解决方案

因此，您需要所谓的“汇总总计”——但仅限于一列。那是部分汇总。此外，您需要每对（代码、公司）的总数，而不是每个单独代码或单独公司的总数。这在 Oraclerollup语法中是可能的，从技术上讲，它是一个“复合列”（除了汇总所需的括号外，语法是将对括在自己的括号内）。用单括号替换关键字后的双rollup括号并比较结果 - 这不是您想要的。

所以，这里是查询：

select   location,code,company,sum(price)
from     sample_tab
group by location,rollup((code,company))   -- partial rollup on composite column
order by location,code,company;            -- add code and company to order by

推荐阅读