Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
24、查询学生平均成绩及其名次
答：SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) AS 平均成绩
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成绩 desc;
——————————————————————————–
网上找了一下，没有分析的文章，或许太简单了。那我们自己来分析。这里涉及到一个SELECT 表1.* FROM 表1 where 表1.字段=表2.字段的表遍历匹配的问题（表1、表2是数据库中同一个表或同一个表查询结果的别名）。
但是代码一大堆，看得一头雾水，我们先拆开来，看T1表的查询(先去了COUNT有助于我们的分析)。
view plaincopy to clipboardprint?
SELECT distinct 平均成绩 FROM (SELECT S#,AVG(score) AS 平均成绩
FROM SC GROUP BY S#) T1
SELECT distinct 平均成绩 FROM (SELECT S#,AVG(score) AS 平均成绩
FROM SC GROUP BY S#) T1
图 T1
接着看，T2表的查询。(加入”order by 平均成绩”，可以让之后的对比效果更明显)
view plaincopy to clipboardprint?
SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# order by 平均成绩
SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# order by 平均成绩
图 T2
对比T1和T2的SQL查询语句及结果，可以看出，除了distinct的效果以外，其他代码基本上一致。
拆分开来看，是简单的SQL语句，那么联合起来是一种什么效果呢？语句执行的顺序又是什么呢？
我们参照图T1和图T2一步步来描述语句的执行顺序。
view plaincopy to clipboardprint?
循环第一次
for (select @i=1 from T2 order by 平均成绩 desc)
{
//取出 T2.平均成绩 = 87
if(T1.平均成绩 > T2.平均成绩) //即扫一遍T1看下有没有大于87的
{
return count(T1.平均成绩); //返回0个（T1中没有大于87的）
}
}
循环第二次
for (select @i=2 from T2 order by 平均成绩 desc)
{
//取出 T2.平均成绩 = 85
if(T1.平均成绩 > T2.平均成绩) //即扫一遍T1看下有没有大于85的
{
return count(T1.平均成绩); //返回1个（T1中刚好有87，大于T2的85）
}
}
循环第三次
for (select @i=3 from T2 order by 平均成绩 desc)
{
//取出 T2.平均成绩 = 85
if(T1.平均成绩 > T2.平均成绩) //即扫一遍T1看下有没有大于85的
{
return count(T1.平均成绩); //返回1个（T1中刚好有87，大于T2的85）
}
}
循环第四次
for (select @i=4 from T2 order by 平均成绩 desc)
{
//取出 T2.平均成绩 = 83
if(T1.平均成绩 > T2.平均成绩) //即扫一遍T1看下有没有大于85的
{
return count(T1.平均成绩); //返回2个（T1中刚好有87,85，大于T2的83）
}
}
以此类推… 直到遍历匹配完毕。
循环第一次
for (select @i=1 from T2 order by 平均成绩 desc)
{
//取出 T2.平均成绩 = 87
if(T1.平均成绩 > T2.平均成绩) //即扫一遍T1看下有没有大于87的
{
return count(T1.平均成绩); //返回0个（T1中没有大于87的）
}
}
循环第二次
for (select @i=2 from T2 order by 平均成绩 desc)
{
//取出 T2.平均成绩 = 85
if(T1.平均成绩 > T2.平均成绩) //即扫一遍T1看下有没有大于85的
{
return count(T1.平均成绩); //返回1个（T1中刚好有87，大于T2的85）
}
}
循环第三次
for (select @i=3 from T2 order by 平均成绩 desc)
{
//取出 T2.平均成绩 = 85
if(T1.平均成绩 > T2.平均成绩) //即扫一遍T1看下有没有大于85的
{
return count(T1.平均成绩); //返回1个（T1中刚好有87，大于T2的85）
}
}
循环第四次
for (select @i=4 from T2 order by 平均成绩 desc)
{
//取出 T2.平均成绩 = 83
if(T1.平均成绩 > T2.平均成绩) //即扫一遍T1看下有没有大于85的
{
return count(T1.平均成绩); //返回2个（T1中刚好有87,85，大于T2的83）
}
}
以此类推… 直到遍历匹配完毕。
清晰理解了这一点，相信整段SQL代码就不难理解了。完整查询结果如下：
另外需要补充一点的是distinct在这里的使用效果。
需不需要distinct，要看排名的要求方式，要distinct是指顺序排名（如上面的例子，则为1,2,2,3…)，不要是指跳序排名（如上面的例子，则为1,2,2,4…)。可见后者其实就是我们日常成绩的排名方式。
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/djlzxzy/archive/2009/02/16/3897069.aspx