SQL之TOP N问题

昨天遇到一个比较有趣的问题：如何在一张表中通过SQL查询出某rank字段的第二大的值？

当时没怎么想清楚，后来回来查了一下资料，现总结如下。

这里选取我在项目中的一张表hy_scm_salescontractdetail，其中有一个整形字段indexnum。

首先，若是仅需找出第二大的值，即是找出比最大值小的子集里的最大值。想通了这点，我们只需利用MAX函数及条件子查询即可。

1 SELECT MAX(indexnum) FROM hy_scm_salescontractdetail 
2 WHERE indexnum < (SELECT MAX(indexnum) FROM hy_scm_salescontractdetail);

现在如果我们将问题延伸一下，如何在一张表中通过SQL查询出某rank字段的TOP N大的值？

在SQL Server中，已经提供了top函数可供使用。但在Oracle中却没有这个函数。

有同学想到了利用Oracle的rownum字段，值得注意的是，该字段是在得到查询结果后系统才加上去的，因此不能直接和ORDER BY一起使用，必须先排序再使用。

1 SELECT indexnum FROM (SELECT * FROM hy_scm_salescontractdetail ORDER BY indexnum DESC)
2 WHERE ROWNUM <= 10;

但是有没有更加通用的方法呢？因为毕竟indexnum只是在Oracle里才会有。

http://hi.baidu.com/zxf_706/item/5c32f4a81ba1aa726cd455bd这位同学提出了一个方法。该方法的思想是，找出比某值大的记录数小于N的记录。实际上就是我们针对每一个rank值，都通过count函数计算出比它大的记录数，然后统计出该记录数小于N的rank值。实现如下：

1 SELECT t1.indexnum FROM hy_scm_salescontractdetail t1
2 WHERE 10>(SELECT COUNT(*) FROM hy_scm_salescontractdetail t2
3 WHERE t1.indexnum<t2.indexnum)ORDER BY t1.indexnum DESC;

但是该方法的效率极其低下，子查询数量与表中的记录数成线性关系，在记录数较大时，其查询时间是不可接受的。

转载于:https://www.cnblogs.com/chrhust/archive/2013/05/15/3080122.html