在一个月黑风高的夜晚，自己无聊学习的SQL的时候，练习，突发奇想的想实现一个功能查询，一张成绩表有如下字段，班级ID，英语成绩，数据成绩，语文成绩如下图
实现 查询出 每个班级英语成绩最高的前两名的记录。
看起来不难的业务，做起来才知道还挺麻烦的，说白了其实就是实现分组后的组内排序，一般不思考的话我们会写出这样的语句：
select top 2 English,Classid from CJ group by Classid order by English desc
出现这个错误，应该就明白了其实数据库的查询顺序是先分组的，最后才将结果进行排序。通过正常逻辑思考，通过班级分组，不就是分了三个组：班级1，班级2，班级3 。我们可以通过聚合函数查询出，每个组的个数，平均值等。可是你后面跟了英语成绩什么鬼？分组之后意味着，我们不能查询单个的记录了，我们查询的单位都是关于组的信息。
第一种实现 1
SELECT * FROM CJ m
where(
select COUNT(*) from CJ n
where m.Classid = n.Classid and n.English > m.English)<2
order by Classid, English desc
也是当网上查的，可以这样理解，要找出前两名的成绩，只要符合比你成绩高的不超过2个人就行了。其实是一个表的自连接，where条件就是一条一条记录对比，首先在m表中拿一条记录，是否符合 在同一班级中 比你成绩高的不超过2个人。这样就可以找到每个班的前两名成绩。然后按照降序排列。
在这种实现中，也可以加上其他筛选条件 比如查询每个班级女生中英语成绩前两名的记录
SELECT * FROM (select * from CJ where Gender='女') m
where(
select COUNT(*) from (select * from CJ where Gender='女') n
where m.Classid = n.Classid and n.English > m.English)<2
order by Classid, English desc
SELECT * FROM CJ m
where(
select COUNT(*) from CJ n
where m.Classid = n.Classid and n.English > m.English and n.Gender='女')<2 --指的是内表
and Gender='女' --指的是外表
order by Classid, English desc
第二种是实现
select a.Classid,a.English from
(select Classid,English,row_number() over(partition by Classid order by English desc) as n
from CJ) a
where n<=2
最官方，最好的实现方式
简单的说row_number()从1开始，为每一条分组记录返回一个数字
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
同样的加上条件
select a.Classid,a.English,n,test from
(select Classid,English,row_number() over(partition by Classid order by English desc) as n,123 test
from CJ where CJ.Gender='女') a
where n<=2
可以看出先执行的是where 进行筛选后，再通过分组，组内再排序，排序后在添加编号，其实是和正常的执行顺序一样的，只不过位置变了
SQL，group by分组后分别计算组内不同值的数量
select name as 姓名,sum( case when cargo='笔' then 1 else 0 end ) as 笔,sum( case when cargo='橡皮' then 1 ...
sql 用Group by分组后,取每组的前几条记录
转自:http://blog.163.com/jeson_lwj/blog/static/135761083201052411115783/ --查询每门课程的前2名成绩 CREATE TABLE S ...
Sql示例说明如何分组后求中间值--【叶子】
原文:Sql示例说明如何分组后求中间值--[叶子] 这里所谓的分组后求中间值是个什么概念呢? 我举个例子来说明一下: 假设我们现在有下面这样一个表: type name price -- ...
MySQL 排名、分组后组内排名、取各组的前几名 及排名后更新插入数据表中
一.排名 /*普通排名:从1开始,顺序往下排*/ AS rank ) r ORDER BY score; /*并列排名:相同的值是相同的排名*/ SELECT cs.* , CASE WHEN @p= ...
group by 分组后 返回的是一个同属性的集合
group by 分组后 返回的是一个同属性的集合 我们可以遍历该集合
MySQL 排名、分组后组内排名、取各组的前几名
一.排名 /*普通排名:从1开始,顺序往下排*/ AS rank ) r ORDER BY score; /*并列排名:相同的值是相同的排名*/ SELECT cs.* , CASE WHEN @p= ...
group by 查询分组后 组的条数
比如select gid from table group by gid 查询时使用下面的方法查询条数 select count(distinct gid) from table 使用select c ...
sql的 group by 分组;linq的 group by 分组
先来看看 linq的,下面的一段linq 是 ,在 学生导入数据的时候,我们根据学生的手机号码和学生名称进行分组,如果有重复的,我们就筛选出来,用到了 linq的 group by,注意这里是new出 ...
随机推荐
Hadoop学习笔记—13.分布式集群中节点的动态添加与下架
开篇:在本笔记系列的第一篇中,我们介绍了如何搭建伪分布与分布模式的Hadoop集群.现在,我们来了解一下在一个Hadoop分布式集群中,如何动态(不关机且正在运行的情况下)地添加一个Hadoop节点与 ...
Frame创建窗体实例
public class Test { public static void main(String[] args) { // TODO Auto-generated method stub Fram ...
OK6410移植madplay播放器,王明学learn
对于ok6410的madplay移植主要包括三部分.声卡驱动移植,播放器的移植,以及alsa库的移植. 一.首先移植声卡驱动以及播放器 ok6410采用WM97系列的声卡芯片,要使得内核支持该驱动,首 ...
ruby Errors &; Exceptions
When you first started coding, errors were probably the last thing you wanted to see. After all, it’ ...
iOS开发——UI篇Swift篇&;玩转UItableView(二)高级功能
UItableView高级功能 class UITableViewControllerAF: UIViewController, UITableViewDataSource, UITableViewD ...
C语言中的结构体和Ｃ＋＋中的结构体以及Ｃ＋＋中类的区别
c++中结构体可以定义一个函数 C中的结构体和C++中结构体的不同之处:在C中的结构体只能自定义数据类型,结构体中不允许有函数,而C++中的结构体可以加入成员函数. C++中的结构体和类的异同: 一. ...
关于ssh登录出现异常警告：WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!
提示警告信息如下: arnold@WSN:~$ ssh 10.18.46.111 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ...
CSS3常用功能的写法 转
CSS3常用功能的写法 作者: 阮一峰 随着浏览器的升级,CSS3已经可以投入实际应用了. 但是,不同的浏览器有不同的CSS3实现,兼容性是一个大问题.上周的YDN介绍了CSS3 Please网站 ...
[leetcode]101. Symmetric Tree对称树
Given a binary tree, check whether it is a mirror of itself (ie, symmetric around its center). For e ...
