select * from (select a.customer_id,sum(a.points_second) num,rownum as rn
from ecc_bj.ecc_points a,ecc_bj.ecc_order b,ecc_bj.ecc_account_card c
where a.customer_id = c.customer_id(+)
and a.customer_id = b.customer_id(+)
and b.status = '104'
group by a.customer_id
order by num desc) t
where t.rn<101
from ecc_bj.ecc_points a,ecc_bj.ecc_order b,ecc_bj.ecc_account_card c
where a.customer_id = c.customer_id(+)
and a.customer_id = b.customer_id(+)
and b.status = '104'
group by a.customer_id
order by num desc) t
where t.rn<101
where rownum<=10 的格式不是top xxx
--------------------------->
select top 100 a.customer_id,sum(a.points_second) num
from ecc_bj.ecc_points a,ecc_bj.ecc_order b,ecc_bj.ecc_account_card c
where a.customer_id = c.customer_id(+)
and a.customer_id = b.customer_id(+)
and b.status = '104'
and rownum<=100 '<---rownum
group by a.customer_id
order by num desc
select a.customer_id,sum(a.points_second) num
from ecc_bj.ecc_points a,ecc_bj.ecc_order b,ecc_bj.ecc_account_card c
where a.customer_id = c.customer_id(+)
and a.customer_id = b.customer_id(+)
and b.status = '104'
and rownum<=100 '<---rownum
group by a.customer_id
order by num desc
from ecc_bj.ecc_points a,ecc_bj.ecc_order b,ecc_bj.ecc_account_card c
where a.customer_id = c.customer_id(+)
and a.customer_id = b.customer_id(+)
and b.status = '104'
group by a.customer_id
order by num desc) t
where t.rn<101
select a.customer_id,sum(a.points_second) num
from ecc_bj.ecc_points a,ecc_bj.ecc_order b,ecc_bj.ecc_account_card c
where a.customer_id = c.customer_id(+)
and a.customer_id = b.customer_id(+)
and b.status = '104'
and rownum<=100 '<---rownum
group by a.customer_id
order by num desc
语句做为嵌套语句放在from里,
select * from (select a.customer_id,sum(a.points_second) num,rownum as rn
from ecc_bj.ecc_points a,ecc_bj.ecc_order b,ecc_bj.ecc_account_card c
where a.customer_id = c.customer_id(+)
and a.customer_id = b.customer_id(+)
and b.status = '104'
group by a.customer_id
order by num desc) t
where t.rn<101
这样一来就不好放了,就上面的句子该怎么改呢,
from
(
select t.* from
(
select a.customer_id,sum(a.points_second) num
from ecc_bj.ecc_points a,ecc_bj.ecc_order b,ecc_bj.ecc_account_card c
where a.customer_id = c.customer_id(+)
and a.customer_id = b.customer_id(+)
and b.status = '104'
group by a.customer_id
order by num desc
) t
where rownum<=100
) maint