# 整型序号产生器(四)

### CUBE 方法

返回4(2^2):

select rownum

from

(

select 1

from   dual

group by cube( 1, 2 )

) ;

ROWNUM

----------

1

2

3

4

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.

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 的参数个数

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

` `

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

• 博文量
72
• 访问量
239332