SQL-ROW_NUMBER() OVER函数的基本用法(源码案例)

     SELECT SUM(t.AdjustedBalance) AS Allqmye FROM 	(	 
	 SELECT * FROM (
		 SELECT 
				ROW_NUMBER() OVER ( PARTITION BY change.AccountSysNo ORDER BY change.indate DESC ) AS RowNumber ,
				(CASE WHEN account.Source=1 THEN account.VendorSysNo ELSE account.DistributorSysNo END ) AS SysNo,
				change.InDate,
                change.AccountSysNo,
				change.AdjustedBalance
         FROM   [BBCFinance].[dbo].[AccountBalanceChange] change
                INNER JOIN BBCAccount.dbo.Account account ON account.SysNo = change.AccountSysNo
				 LEFT JOIN BBCAccount.dbo.Vendor V WITH ( NOLOCK ) ON V.SysNo = Account.VendorSysNo
				 LEFT JOIN BBCAccount.dbo.Distributor D WITH ( NOLOCK ) ON D.SysNo = Account.DistributorSysNo
				
				 ) r  WHERE SysNo=2272 AND InDate >= '2016/11/23 0:00:00' AND InDate <= '2016/11/30 0:00:00'
		   ) t		      WHERE  RowNumber=1



		    SELECT SUM(t.AdjustedBalance) FROM 	(	 
		 SELECT 
				ROW_NUMBER() OVER ( PARTITION BY change.AccountSysNo ORDER BY change.indate DESC ) AS RowNumber ,
				(CASE WHEN Account.Source=1 THEN Account.VendorSysNo ELSE Account.DistributorSysNo END ) AS SysNo,
                change.AccountSysNo,
				change.AdjustedBalance
         FROM   [BBCFinance].[dbo].[AccountBalanceChange] change
                INNER JOIN BBCAccount.dbo.Account account ON account.SysNo = change.AccountSysNo
				 LEFT JOIN BBCAccount.dbo.Vendor V WITH ( NOLOCK ) ON V.SysNo = Account.VendorSysNo
				 LEFT JOIN BBCAccount.dbo.Distributor D WITH ( NOLOCK ) ON D.SysNo = Account.DistributorSysNo
         WHERE  account.VendorSysNo = 2272
		 AND change.InDate >= '2016/11/23 0:00:00' AND change.InDate <= '2016/11/29 23:59:59'  
		   ) t
		   WHERE t.RowNumber=1

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏前端儿

Web 前端颜色值--字体--使用,整理整理

颜色值 CSS 颜色使用组合了红绿蓝颜色值 (RGB) 的十六进制 (hex) 表示法进行定义。对光源进行设置的最低值可以是 0(十六进制 00)。最高值是 2...

2272
来自专栏linux驱动个人学习

高通msm8909耳机调试

1、DTS相应修改: DTS相关代码:kernel/arch/arm/boot/dts/qcom/msm8909-qrd-skuc.dtsi: 1 s...

7455
来自专栏我和未来有约会

简练的视图模型 ViewModel

patterns & practices Developer Center 发布了 Unity Application Block 1.2 for Silver...

2169
来自专栏Pulsar-V

Save Camera Document

#pragma once #include "HCCamera.h" #include <time.h> #include <cstdio> #incl...

2828
来自专栏搞前端的李蚊子

Html5模拟通讯录人员排序(sen.js)

// JavaScript Document  var PY_Json_Str = ""; var PY_Str_1 = ""; var PY_Str_...

5886
来自专栏linux驱动个人学习

高通Audio中ASOC的machine驱动

ASoC被分为Machine、Platform和Codec三大部分,其中的Machine驱动负责Platform和Codec之间的耦合以及部分和设备或板子特定的...

9714
来自专栏高性能服务器开发

(八)高性能服务器架构设计总结3——以flamigo服务器代码为例

再看filezilla,一款ftp工具的服务器端,它采用的是Windows的WSAAsyncSelect模型(代码下载地址:https://github.com...

1181
来自专栏潇涧技术专栏

Tips about MacBook's battery health

内容摘自Mac上的一个灰常好的免费软件Battery Health中关于保养MacBook的电池寿命的建议,与大家一起分享

501
来自专栏一个会写诗的程序员的博客

【Mac IDEA Java Web项目Tomcat启动报错】java.net.MalformedURLException: Local host name unknown

java.net.MalformedURLException: Local host name unknown

773
来自专栏码匠的流水账

聊聊spring cloud的HystrixAutoConfiguration

本文主要研究一下spring cloud的HystrixAutoConfiguration

952

扫码关注云+社区