一些脚本,希望对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_sum
from t3
/
2.取第N条记录
表pp,列a,b,数据如下:
A B
---------- ----------
7 f
6 g
5 e
4 d
3 c
2 b
1 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
http://www.itpub.net/180363.html
