Oracle & Starcraft

cube子句在dw环境用的比较多，特别是在产生交叉报表的情况，演示一个cube的例子
SQL 10G>create table test(sales varchar2(10),dest varchar2(10),revenue number);
Table created.
SQL 10G>insert into test values(’smith’,'hangzhou’,1000);
1 row created.
SQL 10G>insert into test values(’smith’,'wenzhou’,2000);
1 row created.
SQL 10G>insert into test values(’allen’,'wenzhou’,3000);
1 row created.
SQL 10G>insert into test values(’allen’,'wenzhou’,4000);
1 row created.
SQL 10G>commit;
Commit complete.

SQL 10G>select * from test;
SALES DEST REVENUE
———- ———- ———-
smith hangzhou 1000
smith wenzhou 2000
allen wenzhou 3000
allen wenzhou 4000

比如说我们想统计每个sales的总销售收入，每个sales在各个城市的销售收入，另外还想知道每个城市所有sales的销售收入总额，以及所有sales的总收入
就像以下报表
hangzhou wenzhou
allen 7000 7000
smith 1000 2000 3000
1000 9000 10000

那我们运行下面这条语句就行了
SQL 10G>select sales,dest,sum(revenue) from test group by cube(sales,dest);
SALES DEST SUM(REVENUE)
———- ———- ————
10000 所有sales的总销售收入
wenzhou 9000 所有sales在温州的销售收入
hangzhou 1000 所有sales在杭州的销售收入
allen 7000 allen的所有销售收入
allen wenzhou 7000 allen在温州的销售收入
smith 3000 smith所有的销售收入
smith wenzhou 2000 smith在温州的销售收入
smith hangzhou 1000 smith在杭州的销售收入
8 rows selected.

如果不想统计城市这个维度，那么用rollup子句

hangzhou wenzhou
allen 7000 7000
smith 1000 2000 3000
10000

SQL 10G>select sales,dest,sum(revenue) from test group by rollup(sales,dest)
2 ;
SALES DEST SUM(REVENUE)
———- ———- ————
allen wenzhou 7000 allen在温州的销售收入
allen 7000 allen的所有销售收入
smith wenzhou 2000 smith在温州的销售收入
smith hangzhou 1000 smith在杭州的销售收入
smith 3000 smith所有的销售收入
10000 所有sales的总销售收入
6 rows selected.

14:47 | 固定链接 | 评论 (7) | 引用通告 (0) | 记录它 | 计算机与 Internet

固定链接 关闭

http://spaces.msn.com/members/wzwanghai/Blog/cns!1p6cztYuyVBgutMjvxSWkuhw!244.entry

如何用dual表代替sequence

有时候我们需要把一张表的数据导入另一张表，但是需要增加一个递增的列，这时候我们可以采用创建序列来实现，当然使用dual表来模拟出这种效果也是可以的。

create table testdual(a number,b number,c number);

create table testdual_base(b number,c number);

SQL 10G>desc testdual;
Name Null? Type
—————————————– ——– —————————-
A NUMBER
B NUMBER
C NUMBER

SQL 10G>desc testdual_base;
Name Null? Type
—————————————– ——– —————————-
B NUMBER
C NUMBER

insert into testdual_base values(1,1);
insert into testdual_base values(2,2);
insert into testdual_base values(3,3);
insert into testdual_base values(4,4);

commit;

比如说我们要把testdual_base表的数据导入testdual表，testdual表的a列为递增列

SQL 10G> INSERT INTO testdual
2 select lev,b,c from
3 (SELECT LEVEL lev FROM dual CONNECT BY LEVEL < = (select count(*) from testdual_base) ) b ,
4 (select rownum rn,b,c from testdual_base) a
5 where a.rn=b.lev
6 ;

4 rows created.

SQL 10G>commit;
Commit complete.

SQL 10G>select * from testdual;
A B C
———- ———- ———-
1 1 1
2 2 2
3 3 3
4 4 4