SQLServer中Partition By及row_number 函数使用详解

partition  by关键字是分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition  by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

今天群里看到一个问题，在这里概述下：查询出不同分类下的最新记录。一看这不是很简单的么，要分类那就用Group By;要最新记录就用Order By呗。然后在自己的表中试着做出来：

首先呢我把表中的数据按照提交时间倒序出来：

“corp_name”就是分类的GUID（请原谅我命名的随意性）。 OK， 这里按照最开始的想法加上Group By来看一下显示效果：

呃，嗯。这尼玛和想象中的结果不一样啊，看来写代码还是要理性分析问题，意念是无法控制结果滴！

既然要求是不同分类的数据，除了使用Group By之外，还有别的函数能用吗？度娘了一下结果还真有，over（partition by ）函数，那么它和平时用的Group By有什么区别呢？ Group By除了对结果进行单纯的分组之外呢，一般都和聚合函数一起使用，Partition By也具有分组功能，属于Oracle的分析函数，在这里就不详细的不啦不啦不啦了。

看代码：

over(partition by corp_name order by submit_time desc ) as t 。就是按照corp_name分类并按时间倒序出来，"t" 这里一列呢就是不同corp_name类出现的次数，需求是只查询出不同分类的最新提交数据，那么我们只需要针对"t"再进行一次筛选即可：

好啦，结果已经出来，不求各位看官喜欢，但求看在我头像中的胸器望点个赞， 好人一生平安哦！！！

ps：SQL Server数据库partition by 与ROW_NUMBER()函数使用详解

关于SQL的partition by 字段的一些用法心得

先看例子：

if object_id('TESTDB') is not null drop table TESTDB
create table TESTDB(A varchar(8), B varchar(8))
insert into TESTDB
select 'A1', 'B1' union all
select 'A1', 'B2' union all
select 'A1', 'B3' union all
select 'A2', 'B4' union all
select 'A2', 'B5' union all
select 'A2', 'B6' union all
select 'A3', 'B7' union all
select 'A3', 'B3' union all
select 'A3', 'B4'

-- 所有的信息

SELECT * FROM TESTDB
A  B
-------
A1 B1
A1 B2
A1 B3
A2 B4
A2 B5
A2 B6
A3 B7
A3 B3
A3 B4

-- 使用PARTITION BY 函数后

SELECT *,ROW_NUMBER() OVER(PARTITION BY A ORDER BY A DESC) NUM FROM TESTDB
A  B  NUM
-------------
A1 B1 1
A1 B2 2
A1 B3 3
A2 B4 1
A2 B5 2
A2 B6 3
A3 B7 1
A3 B3 2
A3 B4 3

可以看到结果中多出一列NUM 这个NUM就是说明了相同行的个数，比如A1有3个，他就给每个A1标上是第几个。

-- 仅仅使用ROW_NUMBER() OVER的结果

SELECT *,ROW_NUMBER() OVER(ORDER BY A DESC)NUM FROM TESTDB
 A  B   NUM
------------------------
A3 B7  1
A3 B3  2
A3 B4  3
A2 B4  4
A2 B5  5
A2 B6  6
A1 B1  7
A1 B2  8
A1 B3  9

可以看到它只是单纯标出了行号。

-- 深入一点应用

SELECT A = CASE WHEN NUM = 1 THEN A ELSE '' END,B
FROM (SELECT A,NUM = ROW_NUMBER() OVER(PARTITION BY A ORDER BY A DESC) FROM TESTDB) T
A  B
---------
A1 B1
  B2
  B3
A2 B4
  B5
  B6
A3 B7
  B3
  B4

接下来我们就通过几个实例来一一介绍ROW_NUMBER()函数的使用。

实例如下：

1.使用row_number（）函数进行编号，如

select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer

原理：先按psd进行排序，排序完后，给每条数据进行编号。

2.在订单中按价格的升序进行排序，并给每条记录进行排序代码如下：

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

3.统计出每一个各户的所有订单并按每一个客户下的订单的金额 升序排序，同时给每一个客户的订单进行编号。这样就知道每个客户下几单了。

如图：

代码如下：

select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order

4.统计每一个客户最近下的订单是第几次下的订单。

代码如下：

 with tabs as
(
select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order
 )
select MAX(rows) as '下单次数',customerID from tabs group by customerID

5.统计每一个客户所有的订单中购买的金额最小，而且并统计改订单中，客户是第几次购买的。

如图：

上图：rows表示客户是第几次购买。

思路：利用临时表来执行这一操作。

1.先按客户进行分组，然后按客户的下单的时间进行排序，并进行编号。

2.然后利用子查询查找出每一个客户购买时的最小价格。

3.根据查找出每一个客户的最小价格来查找相应的记录。

代码如下：

with tabs as
 (
select ROW_NUMBER() over(partition by customerID order by insDT) as rows,customerID,totalPrice, DID from OP_Order
)
 select * from tabs
where totalPrice in
(
select MIN(totalPrice)from tabs group by customerID
 )

6.筛选出客户第一次下的订单。

思路。利用rows=1来查询客户第一次下的订单记录。

代码如下：

with tabs as
(
select ROW_NUMBER() over(partition by customerID order by insDT) as rows,* from OP_Order
)
select * from tabs where rows = 1
select * from OP_Order

7.rows_number()可用于分页

思路：先把所有的产品筛选出来，然后对这些产品进行编号。然后在where子句中进行过滤。

8.注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。

如下代码：

select
ROW_NUMBER() over(partition by customerID order by insDT) as rows,
customerID,totalPrice, DID
from OP_Order where insDT>'2011-07-22'

以上代码是先执行where子句，执行完后，再给每一条记录进行编号。

赞 (0)

相关推荐

  • sqlserver 通用存储过程分页代码(附使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况性能分析)

    表结构: 复制代码 代码如下: CREATE TABLE [dbo].[Xtest]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [XName] [nvarchar](50) NULL, [XDemo] [nvarchar](500) NULL, CONSTRAINT [PK_xtest] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

  • SqlServer 2005中使用row_number()在一个查询中删除重复记录

    下面我们来看下,如何利用它来删除一个表中重复记录: 复制代码 代码如下: If Exists(Select * From tempdb.Information_Schema.Tables Where Table_Name Like '#Temp%') Drop Table #temp Create Table #temp ([Id] int, [Name] varchar(50), [Age] int, [Sex] bit default 1) Go Insert Into #temp ([Id

  • SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

    ROW_NUMBER() 说明:返回结果集分区内行的序列号,每个分区的第一行从 1 开始.语法:ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) .备注:ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序.参数:<partition_by_clause> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区.       <o

  • sqlserver CONVERT()函数用法小结

    CONVERT的使用方法: 格式: CONVERT(data_type,expression[,style]) 说明: 此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar) 相互转换的时候才用到. 例子: SELECT CONVERT(varchar(30),getdate(),101) now 结果为 now --------------------------------------- 09/15/2001

  • 谈谈sqlserver自定义函数与存储过程的区别

    一.自定义函数: 1. 可以返回表变量 2. 限制颇多,包括 不能使用output参数: 不能用临时表: 函数内部的操作不能影响到外部环境: 不能通过select返回结果集: 不能update,delete,数据库表: 3. 必须return 一个标量值或表变量 自定义函数一般用在复用度高,功能简单单一,争对性强的地方. 二.存储过程 1. 不能返回表变量 2. 限制少,可以执行对数据库表的操作,可以返回数据集 3. 可以return一个标量值,也可以省略return 存储过程一般用在实现复杂的

  • sqlServer使用ROW_NUMBER时不排序的解决方法

    设置sqlServer使用ROW_NUMBER时不排序 --1.看到NHibernate是这样写的分页,感觉写起来比较容易理解(应该不会有效率问题吧?) --with只是定一个别名? [sql] with query as (select ROW_NUMBER() over(order by (select 0)) AS ROWNUM, * FROM Product) select * from query where ROWNUM BETWEEN 5 AND 10 --2.ROW_NUMBER

  • sqlserver2005使用row_number() over分页的实现方法

    语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) <BR> 例子: 复制代码 代码如下: select * from ( select *, ROW_NUMBER() OVER(Order by a.CreateTime DESC ) AS RowNumber from table_name as a ) as b where RowNumber BETWEEN 1 and 5 将会返回table表 其中有一列名字为 RowNu

  • 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(2

  • SQLServer中Partition By及row_number 函数使用详解

    SQLServer中Partition By及row_number 函数使用详解

    partition  by关键字是分析性函数的一部分,它和聚合函数不同的地方在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反映统计值的记录,partition  by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组. 今天群里看到一个问题,在这里概述下:查询出不同分类下的最新记录.一看这不是很简单的么,要分类那就用Group By;要最新记录就用Order By呗.然后在自己的表中试着做出来: 首先呢我把表中的数据按照提交时间倒序出来: "corp_name"就是

  • SQLserver中cube：多维数据集实例详解

    1.cube:生成多维数据集,包含各维度可能组合的交叉表格,使用with 关键字连接 with cube 根据需要使用union all 拼接 判断 某一列的null值来自源数据还是 cube 使用GROUPING关键字 GROUPING([档案号]) = 1 : null值来自cube(代表所有的档案号) GROUPING([档案号]) = 0 : null值来自源数据 举例: SELECT * INTO ##GET FROM (SELECT * FROM ( SELECT CASE WHEN

  • Python中常用的高阶函数实例详解

    前言 高阶函数指的是能接收函数作为参数的函数或类:python中有一些内置的高阶函数,在某些场合使用可以提高代码的效率． lambda 当在使用一些函数的时候,我们不需要显式定义函数名称,直接传入lambda匿名函数即可.lambda匿名函数通常和其他函数搭配使用. 比如可以直接使用如下的lambda表达式计算当x=3时,y = x * 3 + 5的函数值. In [1]: (lambda x: x * 3 + 5)(3) Out[1]: 14 map map函数将一个函数和序列/迭代器(可以传

  • numpy中hstack vstack stack concatenate函数示例详解

    目录 大纲 1.concatenate() 2.stack() 3.vstack() 4.hstack() 5.tf中的stack() 大纲 本文主要介绍一下numpy中的几个常用函数,包括hstack().vstack().stack().concatenate(). 1.concatenate() 我们先来介绍最全能的concatenate()函数,后面的几个函数其实都可以用concatenate()函数来进行等价操作. concatenate()函数根据指定的维度,对一个元组.列表中的li

  • SQLServer中防止并发插入重复数据的方法详解

    SQLServer中防止并发插入重复数据,大致有以下几种方法: 1.使用Primary Key,Unique Key等在数据库层面让重复数据无法插入. 2.插入时使用条件 insert into Table(****) select **** where not exists(select 1 from Table where ****); 3.使用SERIALIZABLE隔离级别,并且使用updlock或者xlock锁提示(等效于在默认隔离级别下使用(updlock,holdlock)或(xl

  • pytorch中的torch.nn.Conv2d()函数图文详解

    pytorch中的torch.nn.Conv2d()函数图文详解

    目录 一.官方文档介绍 二.torch.nn.Conv2d()函数详解 参数dilation——扩张卷积(也叫空洞卷积) 参数groups——分组卷积 总结 一.官方文档介绍 官网 nn.Conv2d:对由多个输入平面组成的输入信号进行二维卷积 二.torch.nn.Conv2d()函数详解 参数详解 torch.nn.Conv2d(in_channels, out_channels, kernel_size, stride=1, padding=0, dilation=1, groups=1,

  • MYSQL row_number()与over()函数用法详解

    MYSQL row_number()与over()函数用法详解

    语法格式:row_number() over(partition by 分组列 order by 排序列 desc) row_number() over()分组排序功能: 在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where .group by.  order by 的执行. 例一: 表数据: create table TEST_ROW_NUMBER_OVER( id varchar(10) not null, name varchar(1

  • C++头文件algorithm中的函数功能详解

    目录 1. 不修改内容的序列操作 (1)all_of (2)any_of (3)none_of (6)find_if (7)find_if_not (8)find_end (10)adjacent_find (12)count_if (15)is_permutation (16)search 2. 修改内容的序列操作 (1)copy (2)copy_n (3)copy_if (4)copy_backward (5)move (6)move_backward (7)swap (8)swap_ran

  • SQLServer之常用函数总结详解

    SQLServer中的常用函数 字符串函数 len() 计算字符串的长度 select LEN(name) from test1 --计算name的长度 大小写转换 lower() upper() select lower('STUDENT !') select upper('student !') 去空 ltrim() 字符串左侧的空格去掉 ,rtrim()字符串右侧的空格去掉 declare @str varchar(100) = ' a a a ' select ltrim(@str) s

  • C++ 中const修饰虚函数实例详解

    C++ 中const修饰虚函数实例详解 [1]程序1 #include <iostream> using namespace std; class Base { public: virtual void print() const = 0; }; class Test : public Base { public: void print(); }; void Test::print() { cout << "Test::print()" << end

随机推荐