频道栏目
首页 > 数据库 > SQL Server > 正文
MSSQL示例——ROWNUMBER、RANK、DENSE_RANK、NTILE排名窗口函数示例
2015-03-02 09:46:29         来源:luckljtchinaren的专栏  
收藏   我要投稿
-- =============================================

-- Author: tomtom
-- Create date: 2015.2.27
-- Description: ROWNUMBER、RANK、DENSE_RANK、NTILE排名窗口函数示例
-- =============================================

/*1.准备数据(去掉注释再执行--!)
create table t1
(
id int identity(1,1) primary key,
name varchar(50),
qty int default(0)
)
go


insert into t1(name,qty) select 'a1',1
insert into t1(name,qty) select 'a3',3
insert into t1(name,qty) select 'a2',2
insert into t1(name,qty) select 'a5',5
insert into t1(name,qty) select 'a2',22
insert into t1(name,qty) select 'a2',21
insert into t1(name,qty) select 'a2',20
insert into t1(name,qty) select 'a1',11
insert into t1(name,qty) select 'a1',12
insert into t1(name,qty) select 'a5',50


select * from t1
*/


/*2.1示例 OVER+ODER BY*/
select t1.*
,ROW_NUMBER() OVER( ORDER BY name,id) as 'ROW_NUMBER'
,ROW_NUMBER() OVER( ORDER BY name desc,id desc) as 'ROW_NUMBER_DESC'
,RANK() OVER( ORDER BY name) as 'RANK'
,RANK() OVER( ORDER BY name,id) as 'RANK_name_id'
,DENSE_RANK() OVER( ORDER BY name) as 'DENSE_RANK'
,t1.name
,NTILE(1) OVER( ORDER BY name,id) as 'NTILE_1group'
,NTILE(2) OVER( ORDER BY name,id) as 'NTILE_2g'
,NTILE(3) OVER( ORDER BY name,id) as 'NTILE_3g'
,NTILE(4) OVER( ORDER BY name,id) as 'NTILE_4g'
--NTILE(分组数目)平均有余时,从最后的组倒分平均:avg=2 for 4,avg=2 for 3,avg=3 for 2 and 1
from t1
order by t1.name,id


/*2.2示例 OVER + PATITION BY + ODER BY*/
--加了PATITION,将会先PATITION成多个分区,然后在分区内部排名,各区的排名互不干扰,排名计数器各自独立从0开始
select t1.*
,ROW_NUMBER() OVER( PARTITION BY name ORDER BY name,id ) as 'ROW_NUMBER'
,ROW_NUMBER() OVER( PARTITION BY name ORDER BY name desc,id desc) as 'ROW_NUMBER_DESC'
,RANK() OVER( PARTITION BY name ORDER BY name) as 'RANK'
,RANK() OVER( PARTITION BY name ORDER BY name,id) as 'RANK_name_id'
,DENSE_RANK() OVER( PARTITION BY name ORDER BY name) as 'DENSE_RANK'
,t1.name
,NTILE(1) OVER( PARTITION BY name ORDER BY name,id) as 'NTILE_1group'
,NTILE(2) OVER( PARTITION BY name ORDER BY name,id) as 'NTILE_2g'
,NTILE(3) OVER( PARTITION BY name ORDER BY name,id) as 'NTILE_3g'
,NTILE(4) OVER( PARTITION BY name ORDER BY name,id) as 'NTILE_4g'
from t1
order by t1.name,id

 

点击复制链接 与好友分享!回本站首页
相关TAG标签 示例 函数
上一篇:[NoSQL]在Linux下安装MongDb
下一篇:PL/SQL不能不学的快捷技能
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站