sqlserver巧用row_number和partition by分组取top数据

分组取TOP数据是T-SQL中的常用查询， 如学生信息管理系统中取出每个学科前3名的学生。这种查询在SQL Server 2005之前，写起来很繁琐，需要用到临时表关联查询才能取到。SQL Server 2005后之后，引入了row_number()函数，row_number()函数的分组排序功能使这种操作变得非常简单。下面是一个简单示例：


代码如下:

--1.创建测试表
create table #score
(
name varchar(20),
subject varchar(20),
score int
)
--2.插入测试数据
insert into #score(name,subject,score) values('张三','语文',98)
insert into #score(name,subject,score) values('张三','数学',80)
insert into #score(name,subject,score) values('张三','英语',90)
insert into #score(name,subject,score) values('李四','语文',88)
insert into #score(name,subject,score) values('李四','数学',86)
insert into #score(name,subject,score) values('李四','英语',88)
insert into #score(name,subject,score) values('李明','语文',60)
insert into #score(name,subject,score) values('李明','数学',86)
insert into #score(name,subject,score) values('李明','英语',88)
insert into #score(name,subject,score) values('林风','语文',74)
insert into #score(name,subject,score) values('林风','数学',99)
insert into #score(name,subject,score) values('林风','英语',59)
insert into #score(name,subject,score) values('严明','英语',96)
--3.取每个学科的前3名数据
select * from
(
select subject,name,score,ROW_NUMBER() over(PARTITION by subject order by score desc) as num from #score
) T where T.num <= 3 order by subject
--4.删除临时表
truncate table #score
drop table #score

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

赞 (0)

相关推荐

  • SQL分组排序去重复的小实例

    复制代码 代码如下: SELECT    *FROM    (        SELECT            userid,            classid,            remark,            ROW_NUMBER () OVER (                PARTITION BY userid,                classid            ORDER BY                addtime DESC        

  • sql分组后二次汇总(处理表重复记录查询和删除)的实现方法

    --处理表重复记录(查询和删除) /****************************************************************************************************************************************************** 1.Num.Name相同的重复值记录,没有大小关系只保留一条 2.Name相同,ID有大小关系时,保留大或小其中一个记录 整理人:中国风(Roy) 日期:2008

  • sql 分组查询问题

    情景一: 表中数据 name score aaa 11 aaa 19 bbb 12 bbb 18 ccc 19 ddd 21 期望查询结果如下 name score aaa 30 bbb 30 ccc 19 ddd 21 复制代码 代码如下: ---检查表是否存在 if exists(select * from sysobjects where name='testSum') drop table testSum go ---创建表 create table testSum ( tid int

  • 以数据库字段分组显示数据的sql语句(详细介绍)

    复制代码 代码如下: <?php$dsn = "mysql:host=localhost;dbname=group";$db = new PDO($dsn, 'root', '');$db->exec('set names utf8'); $sql = "select conf_id from  `group` group by conf_id";//$pre = $db->prepare($sql);//$result_arr = array()

  • SQLserver 实现分组统计查询（按月、小时分组）

    SQLserver 实现分组统计查询（按月、小时分组）

    设置AccessCount字段可以根据需求在特定的时间范围内如果是相同IP访问就在AccessCount上累加. 复制代码 代码如下: Create table Counter ( CounterID int identity(1,1) not null, IP varchar(20), AccessDateTime datetime, AccessCount int ) 该表在这儿只是演示使用,所以只提供了最基本的字段 现在往表中插入几条记录 insert into Counter selec

  • SQL中Group分组获取Top N方法实现可首选row_number

    有产品表,包含id,name,city,addtime四个字段,因报表需要按城市分组,统计每个城市的最新10个产品,便向该表中插入了100万数据,做了如下系列测试: 复制代码 代码如下: CREATE TABLE [dbo].[products]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) NULL, [addtime] [datetime] NULL, [city] [nvarchar](10) NULL, CONSTRA

  • 一句Sql把纵向表转为横向表，并分别分组求平均和总平均值

    一句Sql把纵向表转为横向表，并分别分组求平均和总平均值

    效果如图所示:测试sql语句如下: 复制代码 代码如下: declare @tab table(Class varchar(20),Student varchar(20),Course varchar(50),Quantity decimal(7,2)); insert into @tab(Class,Student,Course,Quantity) values('A班','张三','语文',60); insert into @tab(Class,Student,Course,Quantity

  • 分组后分组合计以及总计SQL语句（稍微整理了一下）

    分组后分组合计以及总计SQL语句（稍微整理了一下）

    今天看到了这个文章感觉内容挺多的,就是比较乱,实在不好整理,我们小编就简单整理了一下,希望大家能凑合看吧 分组后分组合计以及总计SQL语句   1)想一次性得到分组合计以及总计,sql: SELECT 分组字段 FROM 表 GROUP BY 分组字段 compute sum(COUNT(*)) 2)分组合计1: SELECT COUNT(*) FROM (SELECT 分组字段 FROM 表 GROUP BY 分组字段 )别名 3)分组合计2: SELECT COUNT(*) FROM (SE

  • Sql Server 分组统计并合计总数及WITH ROLLUP应用

    WITH ROLLUP 在生成包含小计和合计的报表时,ROLLUP 运算符很有用.ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集. 复制代码 代码如下: SELECT [Source], COUNT(*) AS OrderTotal FROM [ExternalOrder] Where OrderStatus=1 AND (CheckPayment=1 ) and TicketDate >= '2012-11-1' AND TicketDate < '2012-12-1

  • SQL语句分组获取记录的第一条数据的方法

    SQL语句分组获取记录的第一条数据的方法

    使用Northwind 数据库 首先查询Employees表 查询结果: city列里面只有5个城市 使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 先进行分组 注:根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的). sql语句为: select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,City,ROW_NUM

  • SQL进行排序、分组、统计的10个新技巧分享

    1.使用排序使数据有序通常,你的所有数据真正需要的仅仅是按某种顺序排列.SQL的ORDER BY语句可以以字母或数字顺序组织数据.因此,相似的值按组排序在一起.然而,这个分组时排序的结果,并不是真的分组.ORDER BY显示每条记录而分组可能代表很多记录.2.进行分组除去重复值 排序和分组之间的最大区别是:排序的数据显示所有记录(在限定标准范围之内),而分组数据不是显示所有记录.GROUP BY语句对于同样的值只显示一条记录.例如,下面的语句中的GROUP BY语句对数据源中重复出现的数据只返回

  • Sql Server：多行合并成一行，并做分组统计的两个方法

    复制代码 代码如下: --创建 test 表 ,插入数据 CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)INSERT test SELECT '001', 'aa',1UNION ALL SELECT '001', 'bb',2UNION ALL SELECT '002', 'aaa',4UNION ALL SELECT '002', 'bbb',5UNION ALL SELECT '002', 'ccc

  • 显示同一分组中的其他元素的sql语句

    显示同一分组中的其他元素的sql语句

    1.原始查询表结果  2.理想查询表结果 一很牛的朋友写的sql语句大笑,学习啦偷笑: select userpwd,username=stuff((select ','+username from tuser t1 where t1.userpwd=t.userpwd for xml path('')),1,1,'') from tuser t group by userpwd 说明: a.stuff方法(举例说明:select stuff('abcdef', 2, 3, 'klmn'); 在

  • SQL SERVER 分组求和sql语句

    SQL SERVER 分组求和sql语句

    需求:如下图所示 实现sql语句 SELECT A1,SUM(A2*A3) FROM A GROUP BY A1 大家可以自行测试一下,主要需要了解group by语句的用法

  • sql获取分组排序后数据的脚本

    先上一段代码,如下所示: 复制代码 代码如下: select distinct  b.OrgID,b.CompanyID,b.AreaID,b.CustChannelID,b.CustID,b.SaleTotalQty,GETDATE() from KDS400TelSurveyCustAll_Temp  as tmp1 cross apply (select top 10 percent * from KDS400TelSurveyCustAll_Temp tmp2 where tmp1.Or

随机推荐