Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > Access数据库-查询/SQL语句

[access查询]关于查询结果排定名次的讨论

时 间:2011-08-19 00:00:00
作 者:都市侠影   ID:20205  城市:成都
摘 要:关于查询结果排定名次的讨论
正 文:

附件如下:

点击下载此附件

坛友“小一”提出利用查询对成绩表中的学生成绩排定名次的问题,在此先把最终的查询(即是附件中的“查询4”)语句写下:

原表为:

查询语句:

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

查询结果为:

只需要结果的朋友们看到此就可以了。本文主要是想讲一下这个查询是怎么想出来的,我觉得写语句并不重要,重要的在于实现查询的思路。因为我也是尝试了很多遍才写出这个最终的查询,附件中的查询1、查询2、查询3都是试过的查询,是一步一步改进而来的。

下面是改进查询的过程:

首先想到如果能够为查询增加一个自动编号字段的话就再好不过了,这样可以把成绩表按成绩降序排列后加上编号就是名次(暂时没考虑到多人有相同成绩从而有相同名次的问题),遗憾的是SQL Server 2005提供的像ROW_NUMBER()具有此功能的函数并不是SQL的标准函数,在Access环境下不可能使用,所以此思路被放弃。

接下来一想,名次这东西,如果你是第五,说明成绩比你高的有4个人(本来应该是4个不同的分数,可能是4个以上的人,但此时还没考虑到),所以想到了把表与自身作一个连接,名次就是比自己分数高的人数加1,于是产生了查询1:

Select a.学号, a.姓名, COUNT(*)+1 AS 名次
FROM 成绩表 AS a, 成绩表 AS b
Where a.成绩 GROUP BY a.学号, a.姓名;

查询结果:

这个查询结果有两个问题:1、学号为5的学生关羽从查询结果中消失了。2、王五与张飞并列第2,但第3名没人。

分析一下,其实原因很简单,因为连接的条件是:a.成绩

第一个问题好解决,干脆把连接条件改成:a.成绩<=b.成绩,即是说,你的名次就是成绩大于等于你的人数,而不是大于你人数加1,这样就是查询2:

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

查询结果:

可以看到,关羽是显示出来了,但是张飞与王五成了并列第3,第2名又不见了,原因也不难从连接条件上分析而得。怎么解决呢?到现在为止,我们一直是认为一个人的名次就是成绩大于等于他的人数,想想如果不是人数,而是比自己高的分数个数那不是更好吗,这样避免了多人同一个分数造成名次问题很麻烦,从而有可能从根本上解决这个问题。首先查询所有不同成绩的查询(就是查询3)如下:

Select DISTINCT 成绩
FROM 成绩表;
DISTINCT就是查询结果中相同值只显示一条的限定词。

查询结果:

可以看到92只显示了一次,于是把原来的表与这个查询结果来作连接,查询条件不变,得到查询4:


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


查询结果:

到此,一切OK!



Access软件网官方交流QQ群 (群号:54525238)       Access源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助