select * from (select a.accountno ,a.dfhm,count(a.accountno ),sum(a.crbalance) from a,b
where a.craccno ='"+accountno+"'
and a.crflag='2'
and b.accountno = a.accountno
group by a.accountno ,a.dfhm
order by sum(a.crbalance) desc )
where rownum=3
where a.craccno ='"+accountno+"'
and a.crflag='2'
and b.accountno = a.accountno
group by a.accountno ,a.dfhm
order by sum(a.crbalance) desc )
where rownum=3
17:24:16 SQL> select * from t1;COL1 ID
---------- ----------
A 1
A 2
A 3
B 4
B 5
B 6
B 7
C 8
c 9
d 10
e 11已选择11行。已用时间: 00: 00: 00.78
17:24:26 SQL> select col1,sum(id),rank() over(order by sum(id) desc ) from t1 group by col1;COL1 SUM(ID) RANK()OVER(ORDERBYSUM(ID)DESC)
---------- ---------- ------------------------------
B 22 1
e 11 2
d 10 3
c 9 4
C 8 5
A 6 6已选择6行。已用时间: 00: 00: 00.63
17:24:31 SQL> select * from (
17:24:39 2 select col1,sum(id) sumid,rank() over(order by sum(id) desc) rk
17:24:59 3 from t1 group by col1)
17:25:07 4 where rk<4;COL1 SUMID RK
---------- ---------- ----------
B 22 1
e 11 2
d 10 3已用时间: 00: 00: 00.31
where a.craccno ='"+accountno+"'
and a.crflag='2'
and b.accountno = a.accountno
group by a.accountno ,a.dfhm
order by sum(a.crbalance) desc ) Where Rownum<=3
select * from (select a.accountno ,a.dfhm,count(a.accountno ),sum(a.crbalance) from a,b
where a.craccno ='"+accountno+"'
and a.crflag='2'
and b.accountno = a.accountno
group by a.accountno ,a.dfhm
order by sum(a.crbalance) desc )
where rownum between 1 and 3
(
select a.accountno,a.dfhm,count(a.accountno) c1,sum(a.crbalance) s1,rank() over(order by sum(a.crbalance) desc) rk from a,b
where a.craccno ='"+accountno+"'
and a.crflag='2'
and b.accountno = a.accountno
group by a.accountno ,a.dfhm
) t where rk<=3;
Oracle9 i SQL ReferenceIf you embed the ORDER BY clause in a subquery and place the ROWNUM condition in
the top-level query, then you can force the ROWNUM condition to be applied after the
ordering of the rows. For example, the following query returns the 10 smallest
employee numbers. This is sometimes referred to as a "top-N query":
SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;
where a.craccno ='"+accountno+"'
and a.crflag='2'
and b.accountno = a.accountno
group by a.accountno ,a.dfhm
having(sum(a.crbalance)>=1000)
order by sum(a.crbalance) desc