现在有这样一张score表,需要计算出每个学生的总分排名
SELECT m.name,m.total,count(n.total) rank
FROM score m,score n
where m.total<n.total or(m.name=n.name AND m.total=n.total)
GROUP BY m.name
ORDER BY m.total desc
这个语句的查询结果如下:
那么这个语句是如何工作的呢?
上面两张表分别是m表和n表,这里使用了表格的自连接,具体方法是:m表逐行与n表逐行对比,统计n表大于m表的数量,或者m表与n表数据相同的数量。
(自连接(self join)是SQL语句中经常要用的连接方式,使用自连接可以将自身表的一个镜像当作另一个表来对待,从而能够得到一些特殊的数据。)
- 以张三为例,m表中取出张三的信息,跟n表中的5条数据进行对比,发现
(1)n表没有大于张三的总分,因此这部分的count值就是0
(2)n表中有1条数据与张三相同,因此这部分的count值就是1
因此张三的排名就是(0+1)=1
- 以李四为例,m表中取出李四的信息,跟n表中的5条数据进行对比,发现
(1)n表有2条数据大于李四的总分,因此这部分的count值就是2
(2)n表中有1条数据与李四相同,因此这部分的count值就是1
因此李四的排名就是(2+1)=3
看到这里,就很容易理解排名方法的精髓了。
现在你已经掌握了sql语句的计算排名方法,关于排名的所有问题都是在此基础上延伸的。来小试牛刀吧~
1、找出某个学生如“麻子”的准确排名
SELECT * FROM (
SELECT m.name,m.total,count(n.total) rank
FROM score m,score n
where m.total<n.total or(m.name=n.name AND m.total=n.total)
GROUP BY m.name
ORDER BY m.total desc) x
WHERE x.name = '麻子'
2、找出某个排名的学生如“排名第4”的学生
SELECT * FROM (
SELECT m.name,m.total,count(n.total) rank
FROM score m,score n
where m.total<n.total or(m.name=n.name AND m.total=n.total)
GROUP BY m.name
ORDER BY m.total desc) x
WHERE x.rank = 4
3、找出学生的前3名
SELECT * FROM (
SELECT m.name,m.total,count(n.total) rank
FROM score m,score n
where m.total<n.total or(m.name=n.name AND m.total=n.total)
GROUP BY m.name
ORDER BY m.total desc) x
WHERE x.rank<=3
这三种题的解法都是先查询出所有学生的排名情况,并将查询结果作为新表x,然后在x表中进行查询,非常的简单方便。
文章原创,以自己做自动化测试过程中遇到的问题及解决思路和步骤,供大家思考,以期抛砖引玉,共同进步和提高。