象牙塔的案例和资讯已经很多年没有更新了,实在没有精力更新,需要最新案例请联系客服!
--- 我们没有销售只有技术,不善于也不需要宣传,服务好已有客户足以!---
网站建设-SQL 求和技巧
发布时间:2009年11月30日  |  编辑:象牙塔 技术部  |  点击率:1978次

    懂点网站建设和SQL的几乎都知道用SQL进行求和计算,SQL语句好写,但怎么让结果显示费了点功夫,总结技巧如下:
    先看示例:
Set rs2=Server.Createobject("Adodb.Recordset")
Sql2="Select top 1  SUM(jiaofeijine) as c From shebao"
rs2.Open Sql2,Conn,1,1
if rs2.eof and rs2.bof then
     else
           response.Write rs2("c")
end if
rs2.close
set rs2=nothing

技巧补充

Aggregate functions (like SUM) often need an added GROUP BY functionality.

集合函数(类似SUM)经常需要用GROUP BY来进行功能性的补充。


 

GROUP BY...

GROUP BY... was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values.

GROUP BY...之所以加到SQL中去是因为集合函数(像SUM)每当他们被访问时就会返回集合所有栏目的值,而且没有GROUP BY的话就不能够找出单独一种栏目所累计的值了。

The syntax for the GROUP BY function is:

使用GROUP BY函数的语法为:

SELECT column,SUM(column) FROM table GROUP BY column


 

GROUP BY Example

举例

This "Sales" Table:

这是张名为"Sales"的表:

Company Amount
W3Schools 5500
IBM 4500
W3Schools 7100

And This SQL:

这是条SQL:

SELECT Company, SUM(Amount) FROM Sales

Returns this result:

返回的结果为:

Company SUM(Amount)
W3Schools 17100
IBM 17100
W3Schools 17100

The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem:

上面这些代码几乎是无效的,因为栏目所返回的数值并不属于我们想要的那种合计。使用 GROUP BY子句可以解决这个问题:

SELECT Company,SUM(Amount) FROM Sales

GROUP BY Company

Returns this result:

返回的结果为:

Company SUM(Amount)
W3Schools 12600
IBM 4500


 

HAVING...

HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions.

WHERE关键字在使用集合函数时不能使用,所以在集合函数中加上了HAVING来起到测试查询结果是否符合条件的作用。

The syntax for the HAVING function is:

HAVING的使用语法为:

SELECT column,SUM(column) FROM table

GROUP BY column

HAVING SUM(column) condition value

This "Sales" Table:

这是名为"Sales"的表:

Company Amount
W3Schools 5500
IBM 4500
W3Schools 7100

This SQL:

SQL语句:

SELECT Company,SUM(Amount) FROM Sales

GROUP BY Company

HAVING SUM(Amount)>10000

Returns this result

返回的结果为

Company SUM(Amount)
W3Schools 12600

 

SQL的其它相关计算查询:

sql sum() as where group by
2007-11-10 08:10

select sum(ydrs) As ydrst from lsstj where sid='"&rs("id")&"' and yddate='"&date()&"' group by sid

#################################################
SELECT SUM(column) FROM table

.eg
SELECT SUM(Age) FROM Persons WHERE Age>20
#################################################
SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value
 
.eg 求和后和大于10000的
SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount)>10000
#################################################

  •   上一篇: asp实现将excel中的数据批量导入到
  •   下一篇: 网站建设-ASP时间函数详解大全