连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
1.生成1-10的数字:
SQL> select rownum from dual connect by rownum <11;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
已选择10行。
2.生成5-10的数字:
SQL> select rownum+5 from dual connect by rownum <6;
ROWNUM+5
----------
6
7
8
9
10
3.生成4个随机数:
SQL> select dbms_random.random from dual connect by rownum <5;
RANDOM
----------
-1.272E+09
1091597786
144717231
1903280455
4.随机选出2行数据:
SQL> create table test ( id number);
表已创建。
SQL> insert into test values (1);
已创建 1 行。
SQL> insert into test values (11);
已创建 1 行。
SQL> insert into test values (111);
已创建 1 行。
SQL> insert into test values (1111);
已创建 1 行。
SQL> insert into test values (11111);
已创建 1 行。
SQL> insert into test values (111111);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from test;
ID
----------
1
11
111
1111
11111
111111
已选择6行。
SQL> select tt.id from (
2 select t.*,dbms_random.random rd,rownum rn from test t order by rd) tt wher
e rownum<3;
ID
----------
11
111111
5.记录分组,每组取前3行:
SQL> select * from test;
ID NAME
---------- --------------------
1 aaa
1 bbb
1 ccc
1 ddd
2 dfdjfk
3 dddddjfk
4 dddddjfk
4 j45djfk
2 39uijdf
2 333uijdf
2 33@IIf
4 33@IddIf
4 j93nmf
3 j9887eef
3 j^&*7eef
3 j^djkieeef
3 j((ieeef
4 j899f
3 ###9f
已选择19行。
SQL> select *
2 from (select id, name, row_number() over(partition by id order by id) rn
3 from test)
4 where rn <= 3;
ID NAME RN
---------- -------------------- ----------
1 aaa 1
1 bbb 2
1 ccc 3
2 dfdjfk 1
2 33@IIf 2
2 333uijdf 3
3 dddddjfk 1
3 ###9f 2
3 j((ieeef 3
4 dddddjfk 1
4 j45djfk 2
4 33@IddIf 3
已选择12行。
行转列:
select a,b,c from
(with test as (select 'aaa' a,'bbb' b,'1,2,3' c from dual)
select a,b,substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) - (instr(t.ca, ',', 1, c.lv) + 1)) AS c
from (select a,b,',' || c || ',' AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt )
子查询定义:
http://www.itpub.net/viewthread.php?tid=1235717
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23135684/viewspace-630257/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23135684/viewspace-630257/