一些脚本,希望对beginner有所帮助.

1.分类统计

drop table t3

/create table t3(sex char(1) ,-- 性别lei char(2) ,-- 类型je number(10,2)-- 金额)/

-- 要求:按照sex/lei统计金额

-- sql脚本如下:

insert into t3 values ('1','A1',100)

/insert into t3 values ('1','A5',100)/insert into t3 values ('2','A3',100)/insert into t3 values ('2','A1',100)/insert into t3 values ('1','A2',100)/insert into t3 values ('2','A1',100)/insert into t3 values ('1','A4',100)/insert into t3 values ('1','A4',100)/insert into t3 values ('2','A2',100)/insert into t3 values ('1','A5',100)/insert into t3 values ('1','A3',100)/insert into t3 values ('1','A2',100)/commit/select sum(decode(sex,'1',je,0)) sex_1_sum,sum(decode(sex,'2',je,0)) sex_2_sum,sum(decode(lei,'A1',je,0)) lei_a1_sum,sum(decode(lei,'A2',je,0)) lei_a2_sum,sum(decode(lei,'A3',je,0)) lei_a3_sum,sum(decode(lei,'A4',je,0)) lei_a4_sum,sum(decode(lei,'A4',je,0)) lei_a5_sumfrom t3/

2.取第N条记录

表pp,列a,b,数据如下:

A B

---------- ----------7 f6 g5 e4 d3 c2 b1 a现在要取出第二条记录A B---------- ----------6 g

sql如下:

drop table t2

/create table t2(bh number ,note varchar2(10))/insert into t2 values(1,'test1')/insert into t2 values(2,'test3')/insert into t2 values(3,'test4')/commit/

select * from

(select t.*,rownum no from (select * from t2 order by note) t) where no = 2/

drop table pp

/create table pp(a char(1) ,b char(1))/insert into pp values('2','b');insert into pp values('4','d');insert into pp values('3','c');insert into pp values('5','e');insert into pp values('1','a');insert into pp values('7','f');insert into pp values('6','g');commit;

-- 不使用分析函数

select * from (select t.*,rownum no from (select * from pp order by a desc) t)

where no = 2/

-- 使用分析函数

select * from

(select pp.*,dense_rank() over (order by a desc) rank from pp) where rank = 2/

建议多看看piner写的--ORACLE之常用FAQ V1.0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21790/，如需转载，请注明出处，否则将追究法律责任。