如何在 Oracle 中生成组小计和总计?

问题描述:
您想在 Oracle 中计算总计、小计和总计。

解决方案:
Oracle ROLLUP 函数在多个级别执行分组，使用从右到左的方法，通过中间级别向上滚动到任何总计。为了演示 ROLLUP 函数，我们将创建一个表来保存网球运动员以及该运动员获得的 ATP 巡回赛冠军和大满贯冠军。

我们将首先创建满足此需求所需的数据。

示例

-- Drop table
DROP TABLE atp_titles;

-- Create table
CREATE TABLE atp_titles (
  player             VARCHAR2(100) NOT NULL,
  title_type         VARCHAR2(100) NOT NULL,
  titles             NUMBER NOT NULL);

示例

-- insert  ATP tour titles won by the player
INSERT INTO atp_titles VALUES('Roger Federer','ATP Tour Titles',103);
INSERT INTO atp_titles VALUES('Rafael Nadal','ATP Tour Titles',86);
INSERT INTO atp_titles VALUES('Novak Djokovic','ATP Tour Titles',81);
INSERT INTO atp_titles VALUES('Pete Sampras','ATP Tour Titles',64);
INSERT INTO atp_titles VALUES('Andre Agassi','ATP Tour Titles',52);
INSERT INTO atp_titles VALUES('Andy Murray','ATP Tour Titles',46);
INSERT INTO atp_titles VALUES('Thomas Muster','ATP Tour Titles',39);
INSERT INTO atp_titles VALUES('Andy Roddick','ATP Tour Titles',32);

示例

-- insert  grandslam titles won by the player
INSERT INTO atp_titles VALUES('Roger Federer','Grandslams',20);
INSERT INTO atp_titles VALUES('Rafael Nadal','Grandslams',20);
INSERT INTO atp_titles VALUES('Novak Djokovic','Grandslams',17);
INSERT INTO atp_titles VALUES('Pete Sampras','Grandslams',14);
INSERT INTO atp_titles VALUES('Andre Agassi','Grandslams',8);
INSERT INTO atp_titles VALUES('Andy Murray','Grandslams',3);
INSERT INTO atp_titles VALUES('Thomas Muster','Grandslams',1);
INSERT INTO atp_titles VALUES('Andy Roddick','Grandslams',0);

COMMIT;

现在我们来看看插入到 atp_titles 表中的几条记录。

示例

SELECT * FROM atp_titles ORDER BY 1;

输出

Andre Agassi    ATP Tour Titles     52
Andre Agassi    Grandslams          8
Andy Murray Grandslams          3
Andy Murray ATP Tour Titles     46
Andy Roddick    ATP Tour Titles     32
Andy Roddick    Grandslams          0
............................
............................

Oracle ROLLUP 表达式会从右到左生成组小计以及总计。假设我们想利用以上数据来确定球员"罗杰·费德勒"所获得的总冠军数(即 ATP 巡回赛冠军数 + 大满贯冠军数)。

示例

SELECT player,title_type, SUM(titles) AS total_titles
  FROM atp_titles
 WHERE player = 'Roger Federer'
GROUP BY ROLLUP (player,title_type)
ORDER BY player,title_type ;

输出

ROLLUP 函数会为 ROLLUP 函数中列出的"n"列生成 n+1 级小计。在上面的例子中，在按球员和 title_type 进行常规分组后，ROLLUP 函数会汇总所有 title_type 值，以便我们可以看到球员"Roger Federer"的大满贯等级总和。您可以在输出中看到以粗体显示的汇总行。

现在，我们将对表格中的所有球员应用 ROLLUP 函数，如下所示:

SQL:

示例

SELECT player, title_type, SUM(titles) As total
  FROM atp_titles
GROUP BY ROLLUP (player,title_type)
ORDER BY player,title_type;

输出

ROLLUP 函数允许我们执行部分汇总，以减少计算小计的次数。以下部分汇总的输出如下所示:

SQL:

示例

SELECT player, title_type, SUM(titles) As total
  FROM atp_titles
GROUP BY ROLLUP (player,title_type)
ORDER BY player,title_type;

输出

相关文章

• Oracle 11g 和 Oracle 12c 之间的区别
• 如何在 Oracle 中显示打开的游标?
• 如何识别 Oracle 中等待时间最长的 SQL 查询?
• 如何在 Oracle 中访问前一行或后一行的值?
• 如何从 Oracle 中的数据字典表生成数据模型?
• 如何识别 Oracle 中消耗更多资源的 SQL?
• 如何在 Oracle 中显示 SQL 执行进度以及执行计划?
• 如何确定 Oracle 中剩余的 SQL 工作量?
• 如何在 Oracle 中监控实时 SQL 执行统计信息?
• 如何在 Oracle 中限制每个会话的数据库资源?