• 欢迎访问金笔头博客,这是一个菜鸟(伪)程序员的自留地,欢迎访问我的github:点击进入

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)

mysql eason 2399次浏览 4个评论 扫描二维码

语法形式:

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
解释:根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

--创建测试表
create table t_score
( 
        [name] [varchar](20), 
        [subject] [varchar](20), 
        [score] [int] 
);

--插入测试数据
insert into t_score ([name],[subject],[score]) values(N'张三',N'语文',98);
insert into t_score ([name],[subject],[score]) values(N'张三',N'数学',80);
insert into t_score ([name],[subject],[score]) values(N'张三',N'英语',90); 
insert into t_score ([name],[subject],[score]) values(N'李四',N'语文',88); 
insert into t_score ([name],[subject],[score]) values(N'李四',N'数学',86); 
insert into t_score ([name],[subject],[score]) values(N'李四',N'英语',88); 
insert into t_score ([name],[subject],[score]) values(N'李明',N'语文',60); 
insert into t_score ([name],[subject],[score]) values(N'李明',N'数学',86); 
insert into t_score ([name],[subject],[score]) values(N'李明',N'英语',88); 
insert into t_score ([name],[subject],[score]) values(N'林风',N'语文',74); 
insert into t_score ([name],[subject],[score]) values(N'林风',N'数学',99); 
insert into t_score ([name],[subject],[score]) values(N'林风',N'英语',59); 
insert into t_score ([name],[subject],[score]) values(N'严明',N'英语',96); 

数据预览:

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)

select [subject]
     ,[name]
     ,[score]
     ,ROW_NUMBER() over(PARTITION by [subject] order by [score] DESC) as RN 
from t_score;

执行结果如下:

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)

--取每个学科第一名
select * from 
( 
  select [subject]
    ,[name]
    ,[score]
    ,ROW_NUMBER() over(PARTITION by [subject] order by [score] DESC) as RN 
  from t_score
) T where T.RN = 1;

执行结果如下:

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)


金笔头博客, 版权所有丨如未注明 , 均为原创, 转载请注明ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
喜欢 (3)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(4)个小伙伴在吐槽
  1. Looking forward to reading more. Great blog.Much thanks again.
    ottoman coffee table2016-02-10 18:56 Reply 未知操作系统 | 未知浏览器
  2. I really like and appreciate your post.Really looking forward to read more. Great.
    Book your Party Bus2016-02-07 22:19 Reply Windows XP | Firefox 3.5.3
  3. vVm6gV many thanks for sharing source files. many thanks
    crorkservice fiverr2016-02-04 05:39 Reply Windows XP | Internet Explorer 6.0
  4. Very good blog article.Thanks Again. Really Great.
    deep anal2016-02-03 04:58 Reply Windows 7 | Chrome 4.0.221.7