[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.成绩
查询结果:
这个查询结果有两个问题:1、学号为5的学生关羽从查询结果中消失了。2、王五与张飞并列第2,但第3名没人。
分析一下,其实原因很简单,因为连接的条件是:a.成绩
第一个问题好解决,干脆把连接条件改成:a.成绩<=b.成绩,即是说,你的名次就是成绩大于等于你的人数,而不是大于你人数加1,这样就是查询2:
Select a.学号, a.姓名, COUNT(b.成绩) AS 名次
查询结果:
可以看到,关羽是显示出来了,但是张飞与王五成了并列第3,第2名又不见了,原因也不难从连接条件上分析而得。怎么解决呢?到现在为止,我们一直是认为一个人的名次就是成绩大于等于他的人数,想想如果不是人数,而是比自己高的分数个数那不是更好吗,这样避免了多人同一个分数造成名次问题很麻烦,从而有可能从根本上解决这个问题。首先查询所有不同成绩的查询(就是查询3)如下:
Select DISTINCT 成绩
查询结果:
可以看到92只显示了一次,于是把原来的表与这个查询结果来作连接,查询条件不变,得到查询4:
到此,一切OK!
FROM 成绩表 AS a, 成绩表 AS b
Where a.成绩<=b.成绩
GROUP BY a.学号, a.姓名;
FROM 成绩表;
DISTINCT就是查询结果中相同值只显示一条的限定词。
Select a.学号, a.姓名, COUNT(b.成绩) AS 名次
FROM 成绩表 AS a, (Select DISTINCT 成绩 FROM 成绩表) AS b
Where a.成绩<=b.成绩
GROUP BY a.学号, a.姓名;
查询结果:
Access软件网官方交流QQ群 (群号:54525238)
Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access选项卡示例】Ac...(09.09)
- 【Access源码示例】按输入...(09.02)
- 【Access日期区间段查询】...(08.29)
- 【Access日期区间段查询】...(08.27)
- Access怎样才能实现日期时...(08.21)
- 【Access定时打开查询】A...(08.19)
- Access生成固定数量的记录...(08.13)
- Access怎样才能实现日期时...(08.12)
- Access利用导航窗体控件对...(08.03)
学习心得
最新文章
- Access表中的字段名、字段标题...(09.19)
- Access快速开发平台--更改“...(09.18)
- 【中秋及国庆优惠】Access培训...(09.15)
- Access如何将日期型的数值转换...(09.14)
- 英文输入法输入数据中存在单引号引起...(09.11)
- 【Access选项卡示例】Acce...(09.09)
- 让Access光标停留在指定的控件...(09.07)
- 关于Access查询条件里使用通配...(09.06)
- Access报表偷懒制作法--Ac...(09.05)
- Access快速开发平台--窗体数...(09.04)