如何在 Oracle 中使用分组集重命名列

Question

我使用分组集计算总量

SELECT
CASE
    WHEN GROUPING(Name) = 1 THEN 'TOTAL' ELSE Name END,
ID,
SUM(Amount)   
FROM Table1 GROUP BY GROUPING SETS ( (ID, Name), (ID) );

我会得到这样的东西

ID      Name     Amount
11    company1    100
11    company1    200
11    TOTAL       300
22    company2    100
22    company2    200
22    TOTAL       300

但我想将名称"总计"更改为"comany1 - 总计"或"公司 2-总计"例如

ID         Name         Amount
11      company1          100
11      company1          200
11      company1-TOTAL    300
22      company2          100
22      company2          200
22      company2-TOTAL   300

有可能做到吗？我被困了一段时间。谢谢！！！

Answer 1

这将

通过分组集为您提供所需的内容：

with test_table as (
 select 11 ID, 'company1' Name, 100 Amount from dual union all
 select 11 ID, 'company1' Name, 200 Amount from dual union all
 select 22 ID, 'company2' Name, 100 Amount from dual union all
 select 22 ID, 'company2' Name, 200 Amount from dual
)
SELECT
  CASE
      WHEN GROUPING(AMOUNT) = 1 THEN NAME||'-TOTAL' ELSE Name END,
  ID,
  SUM(Amount)
FROM test_table
GROUP BY GROUPING SETS ( (ID, Name, AMOUNT), (ID, NAME));

这是另一种方式：

with test_table as (
 select 11 ID, 'company1' Name, 100 Amount from dual union all
 select 11 ID, 'company1' Name, 200 Amount from dual union all
 select 22 ID, 'company2' Name, 100 Amount from dual union all
 select 22 ID, 'company2' Name, 200 Amount from dual
)
select id, name, amount from (
  SELECT
    ID,
    name,
    Amount
  FROM test_table
  union all
  select id, name || '-TOTAL', sum(amount) from test_table  group by id, name || '-TOTAL'
) order by id, name, amount
;

Answer 2

如何将其表述为：

SELECT (CASE WHEN GROUPING(Amount) = 1 THEN Name || '-TOTAL' ELSE Name END),
       ID, SUM(Amount)   
FROM Table1
GROUP BY GROUPING SETS ( (ID, Name, Amount), (ID, Name) );

这假定Amount是唯一的。 如果没有，也许您有另一个可以使用的唯一列。