[access查询]在查询中显示指定比例的记录数

时　间:2011-08-22 00:00:00
作　者:都市侠影   ID：20205  城市：成都
摘　要:应用中经常要碰到这样的问题，要在查询中按某字段排序后显示前三分之一的记录之类，比如某石油大亨在某石油高校设立了奖学金，获奖比例约为20%，那么获奖名单上应有哪些人呢？这样的事情多半有后门可钻，但是我们的计算机还是能给出一份最初的名单的，剩下的就是某某辅导员酌情增删而已。

正　文:

点击下载此附件

应用中经常要碰到这样的问题，要在查询中按某字段排序后显示前三分之一的记录之类，比如某石油大亨在某石油高校设立了奖学金，获奖比例约为20%，那么获奖名单上应有哪些人呢？这样的事情多半有后门可钻，但是我们的计算机还是能给出一份最初的名单，剩下的就是某某辅导员酌情增删而已。
怎样在查询中实现呢？本文附件中的表如下：

首先想到的是是使用TOP子句，把表中的记录按成绩降序排列后显示前20%的记录数，于是写查询语句如下：

Select TOP 0.2*DCOUNT("*","成绩表")  学号,姓名,成绩 FROM 成绩表 orDER BY 成绩 DESC;

但是系统提示：

又尝试了下：

Select TOP 3  学号,姓名,成绩 FROM 成绩表 orDER BY 成绩 DESC;

运行正常，又尝试了几遍，发现TOP后面为常量时运行正常，若为表达式则提示出错，无奈只好放弃。

转换思路，可否先将查询结果排一个名次，依据此名次来实现呢，下面就是肯定的回答：

我在文章“关于对查询结果排定名次的讨论”一文（地址：http://www.accessoft.com/article-show.asp?id=5723）中写了一个嵌套查询（本文附件中的查询：名次查询），可以根据成绩字段查询各学生的名次，此查询实现了相同的分数有相同的名次。该查询的SQL语句如下：

查询语句（本文附件中的“名次查询”）：

Select a.学号, a.姓名, COUNT(b.成绩) AS 名次
FROM 成绩表 AS a, (Select DISTINCT 成绩 FROM 成绩表) AS b
Where a.成绩<=b.成绩
GROUP BY a.学号, a.姓名;

查询结果：

在此查询基础上，可以实现这里的需求，只要对“名次查询”做一下筛选就行，筛选的依据是选择那些名次与最大名次之比不超过给定比例的学生，这个最大名次又得通过对名次查询的一个查询来实现：
Select MAX(名次) FROM 名次查询;
然后将“名次查询”与这个查询做一个连接（无须连接条件），在连接的结果中筛选记录，选择那些名次与最大名次之比不超过给定比例的记录：
（附件中的“名次靠前查询”）
Select 名次查询.*
FROM 名次查询, (Select MAX(名次) AS 最大名次 FROM 名次查询) AS a
Where 名次查询.名次/a.最大名次<0.2;
这里Where子句里的0.2是设定的20%的比例，查询结果：

如果想设定为其他比例，可以在窗体上设组合框和子窗体，组合框由用户选择比例，子窗体显示记录。
两点说明：
1、之所以没有把“名次查询”和“名次靠前查询”两者合并为一个查询，有两方面的考虑：一是试图用一个查询来实现的话，这个查询要嵌套好几层从而较为复杂，可读性会很差；另一方面名次这样的信息很可能在别的查询中会用到，单独写“名次查询”可以共享名次信息。
2、由于有分数相同的情况，查询中查询出的记录数并不严格等于计算出来的记录数，比如现在有7个学生：一个100分，3个90分，3个80分，如果要按成绩显示前40%的学生，理论上的记录数是7*40%=2.8条记录（显示两条或三条记录理论上都正常），第1名无可争议，但是第2名有3个人都是90分，有什么理由只显示这个不显示那个呢，所以系统做的处理是将第2名的3个人都显示，这样一来实际显示的记录数是略大于理论计算出来的记录数。