CUBE 方法
这里将介绍如何在SELECT语句里使用CUBE 子句产生一系列整数.
例如：
返回4行(2^2):
select rownum
from
(
select 1
from dual
group by cube( 1, 2 )
) ;
ROWNUM
----------
1
2
3
4
返回8行 (2^3):
select rownum
from
(
select 1
from dual
group by cube( 1, 2, 3 )
) ;
ROWNUM
----------
1
2
3
4
5
6
7
8
8 rows selected.
返回9行
select rownum
from
(
select 1
from dual
group by cube( 1, 2, 3, 4 )
)
where
rownum <= 9
;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
我们使用这个方法来处理day of the week 的例子
with days_of_the_week as
(
select rownum - 1 as day_of_week
from ( select 1 from dual group by cube( 1, 2, 3 ) )
where rownum <= 7
)
select
day_of_week ,
t.val
from
days_of_the_week
left outer join t
using ( day_of_week )
order by
day_of_week
;
DAY_OF_WEEK VAL
----------- ----------
0
1 100
2
3 300
4 400
5 500
6
CUBE 的参数个数
要确保你WHERE中返回的记录数小于或等于2^( CUBE 参数个数), 否则你得不到你需要的数。下面的例子中，我们要返回7行，可是只能产生4行。
select
rownum as integer_value
from
(
select 1
from t2
group by cube ( 1, 2 ) – 只产生 2^2 行
)
where
rownum <= 7 – 这里只能 <= 4, 3, 2, 或r 1 in this query, 而不能是 5, 6, 7, ...
;
INTEGER_VALUE
-------------
1
2
3
4
内联视图
不在内联视图里使用 rownum可能不会得到正确的结果：
select rownum
from t2
group by cube( 1, 2 ) ;
select rownum
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
select rownum
from t2
group by rownum, cube( 1, 2 ) ;
ROWNUM
----------
1
1
1
1