数据表C中按A创建分组、按A的分组信息SUM(B),提取A、SUM(B)两个字段后,按SUM(B)列降排序DESC,取前10个最大的记录。
版本是8.0.5
我的方法是:
select *
from (lect A ,sum(B) from C group by a order by b DESC)
where rownum < 100
报错:
missing right parentthesis
版本是8.0.5
我的方法是:
select *
from (lect A ,sum(B) from C group by a order by b DESC)
where rownum < 100
报错:
missing right parentthesis
不支持子查询排序,不支持函数 row_number() rank(),des_rank().请问有什么好的办法??
(select a,sum(b) b from c group by a order by b asc) where rownum<=10
group by 后面的条件,使得数据可以达到唯一
比如: group by 作者,主键id
不知是否可以,试试看吧~~select *
from (select tt.*
from (
select a aa,sum(b) as bb
from C
group by a
)tt
group by tt.bb,tt.aa)
where rownum <= 10;如果按asc排序还好,但按desc排序就 ...
但是您的方法还是可行的。
1.先建立视图:
v_test:
select tt.*
from (
select a aa,sum(b) as bb
from C
group by a
)tt
group by tt.bb,tt.aa)
2。求出V_test的所有记录条数(ll_count)
select count(*) from v_test
3.用ll_count-100(假设为2000)
4.对查询的范围进行截取
select * from (
select row_.*,rownum rownum_ from (
select tt.*,rownum from v_xc_V_testtt ) row_
where rownum<=2100)
where rownum_>2000
5.在程序中对这100条数据进行排序。
不知道能不能用一条语句搞定?
from (select row_.*, rownum rownum_
from (select tt.*, rownum
from (
select tt.*
from (select a aa, sum(b) as bb from C group by a) tt
group by tt.bb, tt.aa
)yy,
(
select count(*) as count_
from (
select tt.*
from (select a aa, sum(b) as bb from C group by a) tt
group by tt.bb, tt.aa
)
)
) row_
where rownum <= count_
)
where rownum_ > count_ - 100;