# 分类统计&取第N条记录

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条记录

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
/

http://www.itpub.net/180363.html

ITPUB数据库版块资深版主，对Oracle、PostgreSQL有深入研究。

• 博文量
1539
• 访问量
4073268