[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！