SqlServer ROW_NUMBER() 排序函数

标签: sql server  函数

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
)

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

思路。利用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

6.rows_number()可用于分页

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

7.注意:在使用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子句,执行完后,再给每一条记录进行编号。


SqlServer四种排序:ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

首先,我们创建一些测试数据。

if OBJECT_ID('Tempdb.dbo.#Tmp') is not null
    drop table #Tmp
create table #Tmp
(
name nvarchar(10)
)

insert into #Tmp
select N'张三'
union
select N'李四'
union 
select N'王五'
union 
select N'赵六'
union
select N'朱七'
union
select N'王八'
union all
select N'张三'

最后一个union用union all,因为我们多一行”张三”。

一、ROW_NUMBER() over(partition by columnname order by columnname)

select ROW_NUMBER()over(order by name) as num,* from #Tmp

可以得到按name排序的结果集。
这里写图片描述

ROW_NUMBER() over()还有一种用法,可以针对某列进行分组排序。

下面结果可以看到张三有1和2两个排序,而其他的名字排序都只有1。

select ROW_NUMBER()over(partition by name order by name) as num,* from #Tmp

这里写图片描述

二、RANK()over(order by columnname)

大家可以从下面的结果集看到,结果集少了5的编号,而有两个4的编号,然后直接跳到编号6。

select RANK()over(order by name),* from #Tmp

这里写图片描述

三、DENSE_RANK()over(order by columnname)

select DENSE_RANK()over(order by name),* from #Tmp

执行Sql后发现,下面的结果集有2个编号4的行,紧接着就是编号5的行。

DENSE_RANK()函数和RANK()函数差不多。

RANK()函数不管分几组,最后的编号一定和行数相同。

DENSE_RANK()函数最后的编号和分组的数目有关。

这里写图片描述

四、NTILE()OVER(ORDER BY COLUMNNAME)

select NTILE(2)over(order by name),* from #Tmp
select NTILE(3)over(order by name),* from #Tmp

NTILE后面的数字,是要把查询得到的结果平均分为几组。
如下图分为2和3组。
如果行数平均划分后还有余行,那么就把行分在最前面的几组上。
比如我们的结果有7行,要分为3组。
那么第一组3行,第二组2行,第三组2行。
如果我们结果有14行,平均分为3组。
那么第一组5行,第二组5行,第三组4行。
依此类推。

这里写图片描述

版权声明:本文为WuLex原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/WuLex/article/details/79288561

智能推荐

Reflect反射的基础知识

写个父类: 写个子类: 利用反射获得该子类中的属性,方法,构造,父类及接口: 运行结果:...

spring cloud netflix (07) 服务的消费者(feign)

前言 完整知识点:spring cloud netflix 系列技术栈 Feign (同步通信 HTTP通信) feign是基于接口完成服务与服务之间的通信的 搭建Feign服务 项目结构 项目搭建 pom.xml application类 application.yml 使用feign完成服务与服务之间的通信 feign是基于接口完成服务与服务之间的通信的...

AtCoder Beginner Contest 174 E.Logs

AtCoder Beginner Contest 174 E.Logs 题目链接 到最后才发现是二分,菜菜的我/(ㄒoㄒ)/~~ 我们直接二分 [1,max{a[i]}][1,max\lbrace a[i]\rbrace][1,max{a[i]}] 即可,对每一个 midmidmid,每个数 a[i]a[i]a[i] 只需要切 a[i]−1mid\frac{a[i]-1}{mid}mi...

小程序基础与实战案例

小程序开发工具与基础 小程序开发准备: 申请小程序账号( appid ) 下载并安装微信开发者工具 具体步骤如下: 先进入 微信公众平台 ,下拉页面,把鼠标悬浮在小程序图标上 然后点击 小程序开发文档 照着里面给的步骤,就可以申请到小程序账号了。 然后就可以下载 开发者工具 了 下载完打开后的界面就是这个样子 下面让我们来新建一个小程序开发项目: 在AppID输入自己刚刚注册的AppID就可以,或...

VMware centOS7 下通过minikube部署Kubernetes

1、环境准备: VMware CentOS-7-x86_64 CPU:2*2core 内存:8G 宿主机和虚拟机需网络互通,虚拟机外网访问正常 Centos发行版版本查看:cat /etc/centos-release root用户操作 2、禁用swap分区 Kubernetes 1.8开始要求关闭系统的Swap,可暂时关闭或永久禁用, 使用 $ free -m 确认swap是否为开启状态 $ s...

猜你喜欢

逻辑回归与scikit-learn

欢迎关注本人的微信公众号AI_Engine LogisticRegression 算法原理 一句话概括:逻辑回归假设数据服从伯努利分布,通过极大化似然函数(损失函数)的方法,运用梯度下降或其他优化算法来求解参数,来达到将数据二分类的目的。 定义:逻辑回归(Logistic Regression)是一种用于解决二分类(0 or 1)问题的机器学习方法,用于估计某种事物的可能性(不是概率)。比如某用户...

指针OR数组?用他们来表达字符串又有何不同?

cocowy的编程之旅 在学习C语言的过程中我们经常可以看到或者听到这样一句话:数组其实等价于指针,例如: 在这里可以轻松的看出输出后他们的值相等,其实在计算机内存里面,p为本地变量,有着他自己的作用域。而指针变量q保存着这个数组的首地址,通过*号指向这个地址保存的变量值。 然而我们再看一个例子: 这个时候计算机报错,这是为什么呢? 其实原因很简单,指针说指向的这个字符串的地址是位于计算机代码段地...

广度搜索

广度搜索的基本使用方法 广度搜索不同于深度搜索,是一种一步一步进行的过程,每一个点只记录一遍。需要用到队列记录每一步可以走到的位置,找到目标位置输出步数即可。 用到的知识:结构体、队列 如图 首先我们需要定义一个结构体来存储每个遍历到的点和步数 广搜不会用到递归,所以可以直接在主函数里写,这里需要定义一个结构体队列 初始化队列并将起始点入列 遍历 完整代码...

NIO Socket 编程实现tcp通信入门(二)

1、NIO简介 NIO面向通道和缓冲区进行工作,数据总是从通道读取到缓冲区中,或者从缓冲区写入到通道中。可以双向传输数据,是同步非阻塞式IO。NIO还引入了选择器机制,从而实现了一个选择器监听多个底层通道,减少了线程并发数。用NIO实现socket的Tcp通信需要掌握下面三个知识点: Buffer 缓冲区 Channel 通道 Selector 选择器   2、java.nio.Buff...

[字节码系列]ObjectWeb ASM构建Method Monitor

      在前面的篇章中,我们看到Java Instrutment的强大能力,本篇,我们将介绍如何使用ObjectWeb ASM的字节码增强能力构建Method Monitor       1.什么是ObjectWeb ASM      ObjectWeb ...