如何为Oracle中指定的维的所有组合生成组小计?
问题陈述:
您想找出Oracle中指定的维的所有组合的小计。
解决方案:
多维数据集功能将为指定尺寸的所有组合生成小计。如果“n”是多维数据集中列出的列数,则将有2n个小计组合。
我们将从为该需求创建必要的数据开始。
示例
-- 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 ............................ ............................
现在让我们将CUBE功能应用于atp播放器-“RogerFederer”
SQL:
示例
SELECT player,title_type, SUM(titles) AS total_titles FROM atp_titles WHERE player = 'Roger Federer' GROUP BY CUBE (player,title_type) ORDER BY player,title_type ;
输出结果
除了由ROLLUP扩展生成的小计外,CUBE扩展还为指定的播放器和标题类型的所有组合生成了小计.CUBE的输出在上方以粗体突出显示。
现在,我们将对表中的所有玩家应用CUBE函数,如下所示:
示例
SELECT player,title_type, SUM(titles) AS total_titles FROM atp_titles GROUP BY CUBE (player,title_type) ORDER BY player,title_type