时间戳

首页 > 解决方案 > 如何在 Oracle 中使用分组集重命名列

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

问题描述

我使用分组集计算总金额

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

但我想将名称“TOTAL”更改为“comany1 -total”或“c​​ompany2-total”，例如

ID         Name         Amount

11      company1          100
11      company1          200
11      company1-TOTAL    300
22      company2          100
22      company2          200
22      company2-TOTAL   300

有可能这样做吗？我坚持了一段时间。谢谢！！！

标签： sqloraclegrouping-sets

解决方案

这将为您提供您想要的分组集：

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
;

推荐阅读