与SQL SERVER相比,Oracle没有Top n 这样简单方便取前几名的方法.
但也有相对应的方法可以实现,使用的是Oracle中的伪列:rownum.
最简单的方法如下:
一:最简单的问题:直接取前几名
SELECT A.* from Company_Expense A WHERE ROWNUM<=N
(注意,在上句中我们没有指定按哪列进行排序,而具体rownum序号是依据哪一列,现在我都不确定。但可以明确一点的是,如果我们按主键进行排序,rownum号不会乱,但如果指定其它列的号,序号有可能会乱)
二:麻烦一点的:取后几名
上面方法的一点变通,首先在内部以某列进行排列,在外面取
select V.* from
(
select rownum,A.* from company_expense A
order by a.expenseid desc
)V where rownum<3
三:变态一点的:取前几名,但值相同的记录为同一名次
比如:对班级学生成绩进行排名,第一名:100,共2名,第二名,98,共5名........如此类推,取出排名前十名的所有学生。
基本思路是:先使用Distinct取得成绩的前十名,并使用rownum排序.这样可以得出第十名的值,然后取所有成绩大于等于第十名的所有学生信息。
记录得到后,然后再与刚使用Distinct取得成绩为前十名,且有rownum序号的表Join,以成绩为条件。就可以得到正常的排名次序。
相关代码请参考如下脚本:
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select rownum, T.Row_Num, v.*
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from (select A.Name,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.Employee_Code,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
v.STATUS,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
To_Char(v.JOIN_DATE, 'yyyy-mm-dd') Join_Date,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.Unit_Name,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
p.line_description Line_Num,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sum(a.last_result) Sum_result
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from QM_QUALITY_LEVEL_JUDGE A,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
hr_lbr_employee_tl_tbl_v v,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Qm_Product_Line P
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Where a.employee_code = v.EMPLOYEE_CODE
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And A.LINE_NUM = P.Line_Num
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And To_Char(A.Index_Date, 'yyyy-mm-dd') >= '2006-05-01'
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And To_Char(A.Index_Date, 'yyyy-mm-dd') <= '2006-07-24'
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And A.Last_Result > 0
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
group by A.Name,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.Employee_Code,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.STATUS,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.JOIN_DATE,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.Unit_Name,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.Line_Num,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
P.LINE_DESCRIPTION
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
order by Sum_Result desc, employee_code desc) v ---基本的信息
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
left join (select rownum row_Num, v.*
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from (select distinct sum(a.last_result) Sum_result
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from QM_QUALITY_LEVEL_JUDGE A,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
hr_lbr_employee_tl_tbl_v v,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Qm_Product_Line P
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Where a.employee_code = v.EMPLOYEE_CODE
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And A.LINE_NUM = P.Line_Num
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And To_Char(A.Index_Date, 'yyyy-mm-dd') >=
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
'2006-05-01'
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And To_Char(A.Index_Date, 'yyyy-mm-dd') <=
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
'2006-07-24'
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And A.Last_Result > 0
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
group by A.Name,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.Employee_Code,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.STATUS,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.JOIN_DATE,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.Unit_Name,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.Line_Num,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
P.LINE_DESCRIPTION
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Order by Sum_Result Desc) v
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Where rownum <= 10) T On v.Sum_Result = T.Sum_Result --得到正确的排名序号
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-----以下条件是取得所有大于第十名成绩的记录
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
where V.sum_Result >=
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(select min(Sum_Result) Sum_Result
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from (select rownum row_Num, v.*
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from (select distinct sum(a.last_result) Sum_result
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from QM_QUALITY_LEVEL_JUDGE A,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
hr_lbr_employee_tl_tbl_v v,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Qm_Product_Line P
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Where a.employee_code = v.EMPLOYEE_CODE
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And A.LINE_NUM = P.Line_Num
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And To_Char(A.Index_Date, 'yyyy-mm-dd') >=
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
'2006-05-01'
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And To_Char(A.Index_Date, 'yyyy-mm-dd') <=
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
'2006-07-24'
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
And A.Last_Result > 0
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
group by A.Name,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.Employee_Code,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.STATUS,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.JOIN_DATE,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
V.Unit_Name,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
A.Line_Num,
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
P.LINE_DESCRIPTION
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Order By Sum_Result Desc) v
![](http://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Where rownum <= 10))