本文为原创,如需转载,请注明作者和出处,谢谢!
先看看下面的表和其中的数据:
t_product
图1
该表有两个字段:xh和price,
其中xh是主索引字段,现在要得到如下的查询结果:
图2
从上面的查询结果可以看出,totalprice字段值的规则是从第1条记录到当前记录的price之和。如第3条记录的totalprice字段的值是10 + 25 + 36 = 71。
现在要通过t_product表中的数据生成图2所示的查询结果。可能会有很多读者想到使用循环和游标,不过这种方式效率并不高,尤其在记录非常多的情况。
从图2的查询结果分析可知,这个结果仍然是求和的操作,只是并不是对所有的记录求和,也不是分组求和,而是使用迭代的方式进行求和,求和的公式如下:
当前记录的totalprice值 = 当前记录的price值 + 上一条记录的totalprice值
上一条记录的totalprice值也可看成是当前记录以前所有记录的price值之和。因此,可以对每一条记录进行求和(使用sum函数),不过要求出当前记录及以前的记录的price之和,如下面的SQL语句:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->selecta.xh,a.price,
(selectsum(price)fromt_productbwhereb.xh<=a.xh)astotalprice
fromt_producta
从上面的SQL语句可以看出,使用了一个子查询来求totalprice字段的值,基本原理就是根据当前记录的xh值(a.xh)来计算从当前记录往前所有记录的price值之和,b.xh表示子查询当前的xh值,在子查询中,a.xh相当于常量。上面的SQL语句的查询结果和图2完全一样。如果我们的需求是不包含当前记录的price值,也就是说,计算totalprice字段的公式如下:
当前记录的totalprice值 = 上一条当前记录的price值 + 上一条记录的totalprice值
第一条记录的totalprice值就是当前记录的price值,查询t_product表的结果如图3所示。
图3
要查询出上述的记录也很容易,只需要将<=改成<即可,SQL语句如下:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->selecta.xh,a.price,
(selectsum(price)fromt_productbwhereb.xh<a.xh)astotalprice
fromt_producta
但上面的SQL查询出来的记录的第一条的totalprice字段值为null,如图4所示。
图4
为了将这个null换成10,可以使用case语句,SQL语句如下:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->selectxh,price,
(casewhentotalpriceisnullthenpriceelsetotalpriceend)astotalprice
from
(selecta.xh,(selectsum(price)fromt_productbwhereb.xh<a.xh)astotalprice,a.price
fromt_producta)x
在上面的SQL语句共有三层select查询,最里面一层如下:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->selectsum(price)fromt_productbwhereb.xh<a.xh)
中间一层的子查询如下:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->selecta.xh,(selectsum(price)fromt_productbwhereb.xh<a.xh)astotalprice,a.price
fromt_producta
最外面一层当然就是整个select语句了。
在执行上面的SQL后,将会得到和图3一样的查询结果了。
如果读者不喜欢写太长的SQL,可以将部分内容写到函数里,代码如下:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->createfunctionmysum(@xhint,@priceint)returnsint
begin
return(select
(casewhentotalpriceisnullthen@priceelsetotalpriceend)astotalprice
from(selectsum(price)astotalpricefromt_productwherexh<@xh)x)
end
可使用下面的SQL语句来使用这个函数:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->selectxh,price,dbo.mysum(xh,price)astotalprice
fromt_product
在执行上面的SQL后,将得出如图3所示的查询结果。
建立t_product表的SQL语句(SQL Server 2005)如下:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
IFNOTEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[t_product]')ANDtypein(N'U'))
BEGIN
CREATETABLE[dbo].[t_product](
[xh][int]NOTNULL,
[price][int]NOTNULL,
CONSTRAINT[PK_t_product]PRIMARYKEYCLUSTERED
(
[xh]ASC
)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY]
)ON[PRIMARY]
END
国内最棒的Google Android技术社区(eoeandroid),欢迎访问!《银河系列原创教程》发布《Java Web开发速学宝典》出版,欢迎定购
分享到:
相关推荐
时间函数,sqlserver,标签系统标签系统标签系统标签系统
自定义聚合函数:用户定义的聚合函数——评估SQL Server 2005中的新功能.pdf
主要介绍了Sqlserver 自定义函数 Function使用介绍,在sqlserver2008中有3中自定义函数:标量函数/内联表值函数/多语句表值函数,需要的朋友可以参考下
18.SQL Server 2014子查询EXISTS.mp4 19.SQL Server 2014返回记录排序.mp4 20.SQL Server 2014关联查询.mp4 21.SQL Server 2014聚合函数AVG() SUM().mp4 22.SQL Server 2014聚合函数MIN() MAX().mp4 23.SQL ...
整理SQL Server系统函数,包含聚合函数、配置函数、日期时间函数、数学函数、字符串等等。简单介绍各函数的使用方法(例子)及函数功能
sqlserver中常用函数----字符串函数---- ASCII() --函数返回字符表达式最左端字符的ASCII 码值 CHAR() --函数用于将ASCII 码转换为字符 --如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值 ...
17.SQL Server 2014子查询IN.mp4 18.SQL Server 2014子查询EXISTS.mp4 19.SQL Server 2014返回记录排序.mp4 2.SQL Server 2014硬件和软件要求.mp4 20.SQL Server 2014关联查询.mp4 21.SQL Server 2014聚合函数...
sql server 常用函数的用法都有详细说明
常用SQL查询技术、比较逻辑运算符查询、SQL关键字查询、表结构与性能、时间与谓词、聚合函数、子查询与连接查询、交叉表、常用数据操作高级应用、试图存储过程和触发器的使用。。。。。。。
sql常用函数 sql server 常用函数
本文主要通过一下几个方面介绍:使用SQLDMV查找慢速查询、通过APM解决方案查询报告、SQLServer扩展事件、SQLAzure查询性能洞察等相关内容。本文来自博客园,由火龙果软件Anna编辑、推荐。SQLServer的一个重要功能是...
Sql server聚合函数在实际工作中应对各种需求使用的还是很广泛的,对于聚合函数的优化自然也就成为了一个重点,一个程序优化的好不好直接决定了这个程序的声明周期。Sql server聚合函数对一组值执行计算并返回单一的...
sql server常用函数大全sql server常用函数大全sql server常用函数大全sql server常用函数大全sql server常用函数大全
SQL Server2000函数大全 sql源码,附说明
利用SUM、AVG、COUNT、COUNT(*)、MAX 和 MIN是无法做到的。因为这些都是对数值的聚合。不过我们可以通过自定义函数的方式来解决这个问题。1.首先建立测试表,并插入测试数据: 代码如下:create table ...
NULL 博文链接:https://yufeng521000.iteye.com/blog/2076607
一个多参数的自定义聚合函数实现方案 ,一个多参数的自定义聚合函数实现方案
在SQL Server中,用户不仅可以使用标准的内置函数,也可以使用自己定义的函数来实现一些特殊的功能。用户自定义函数可以在企业管理器中创建,也可以使用CREATE FUNCTION 语句创建。在创建时需要注意:函数名在数据库...
SQLServer基本函数.字符串函数,数学函数,日期函数 ,系统函数等
主要介绍了SQLServer行列互转实现思路,使用聚合函数pivot/unpivot实现行列互转,感兴趣的小伙伴们可以参考一下