一个简单的数据: create table t(bm varchar2(2),xl number(5,2)); insert into t values(10,5) insert into t values(11,4) insert into t values(12,8) insert into t values(10,7) insert into t values(13,10.2) insert into t values(12,20) insert into t values(14,88) insert into t values(13,10)正确的排名次序(所有) select bm,sum(xl)xl from t group by bm order by xl desc BM XL -------- --------- 14 88 12 28 13 20.2 10 12 11 4如果按这种方式指定排名次序前三名倒排: select * from(select bm,sum(xl)xl from t group by bm) where rownum<4 order by xl desc;BM XL -------- --------- 12 28 10 12 11 4结果肯定不对。 如果rownum 小如总记录数,SQL语名不能查询所有记录。
谢谢qiuyang啊,对了我怎么给你分啊
我还想问你,你有没有关于ora学习的建议啊告诉我啊 !
用order 和 rownum 根本就实现不了排名
create table t(bm varchar2(2),xl number(5,2));
insert into t values(10,5)
insert into t values(11,4)
insert into t values(12,8)
insert into t values(10,7)
insert into t values(13,10.2)
insert into t values(12,20)
insert into t values(14,88)
insert into t values(13,10)正确的排名次序(所有)
select bm,sum(xl)xl from t group by bm order by xl desc
BM XL
-------- ---------
14 88
12 28
13 20.2
10 12
11 4如果按这种方式指定排名次序前三名倒排:
select * from(select bm,sum(xl)xl from t group by bm)
where rownum<4 order by xl desc;BM XL
-------- ---------
12 28
10 12
11 4结果肯定不对。
如果rownum 小如总记录数,SQL语名不能查询所有记录。
感谢大家的参与,每个参与者都给分啊!
select * from (
select * from (select 组编码,sum(销售额) 销售额 from tab_1 group by 组编码) order by 销售额 DESC
)
where rownum <= 20 ;
这样行了吧。
from
(select 组编码,销售额,rank() over (partition by 组编码 order by 销售额) ranknum from 销售记录表
(
where ranknum<=20;
回-》小数点
在家一层也不可以的。我在WHERE 语句中加大组号的,选择的结果是不对的。
你给的语句我执行了,但报错。ORDER BY 语句丢失右括号。
且在最后的WHERE 语句中不可以加 组号 IN (51,52,53)