order by对rownum不起作用。 但可以变通: select * from (select rownum rn,... from table1 where ...) where rn<=50 order by ...
select * from (select * from table where... order by ...) where rownum<=50
我还是把我的原代码给大家看看: select e.pub_goods_id , decode((sum(a.lendervalue)+sum(b.inivalue))/2,null,0,0,0,2*sum(e.lendervalue) / (sum(a.lendervalue)+sum(b.inivalue))) as operatnum, sum(e.lendervalue) as area, (sum(a.lendervalue)+sum(b.inivalue)) / 2 as avg from deptware a , deptware_daily b , pub_goods d , deptwarelist e where b.pub_goods_id = a.pub_goods_id (+) and a.pub_goods_id = e.pub_goods_id and b.pub_goods_id = d.pub_goods_id and e.btype_id = 1 and a.subcono = b.subcono and a.dept = b.dept and b.subcono = :an_subcono and b.dept = :an_dept and b.dateaccount >= :as_start and b.dateaccount <= :as_end and d.goods_status = '0' group by e.pub_goods_id order by e.pub_goods_id 大家说怎么取前50位吧!
to beckhambobo(beckham) : 我的oracle是8.0.5 select * from (select data_time from tab_cp_bi) where rownum<=50; 可以select * from (select data_time from tab_cp_bi order by data_time) where rownum<=50; 报错,错误信息: select * from (select data_time from tab_cp_bi order by data_time) * ERROR at line 1: ORA-00907: missing right parenthesis请问,WHY?
ms的t-sql里可以写
select top 50 * from ...
但可以变通:
select * from (select rownum rn,... from table1 where ...)
where rn<=50
order by ...
select e.pub_goods_id ,
decode((sum(a.lendervalue)+sum(b.inivalue))/2,null,0,0,0,2*sum(e.lendervalue) / (sum(a.lendervalue)+sum(b.inivalue))) as operatnum,
sum(e.lendervalue) as area,
(sum(a.lendervalue)+sum(b.inivalue)) / 2 as avg
from deptware a , deptware_daily b , pub_goods d , deptwarelist e
where b.pub_goods_id = a.pub_goods_id (+) and
a.pub_goods_id = e.pub_goods_id and
b.pub_goods_id = d.pub_goods_id and
e.btype_id = 1 and
a.subcono = b.subcono and
a.dept = b.dept and
b.subcono = :an_subcono and
b.dept = :an_dept and
b.dateaccount >= :as_start and
b.dateaccount <= :as_end and
d.goods_status = '0'
group by e.pub_goods_id
order by e.pub_goods_id
大家说怎么取前50位吧!
我的oracle是8.0.5
select * from (select data_time from tab_cp_bi)
where rownum<=50; 可以select * from (select data_time from tab_cp_bi order by data_time)
where rownum<=50; 报错,错误信息:
select * from (select data_time from tab_cp_bi order by data_time)
*
ERROR at line 1:
ORA-00907: missing right parenthesis请问,WHY?