網(wǎng)站建設(shè)-SQL 求和技巧

發(fā)布者：象牙塔編輯部發(fā)布時(shí)間：2009-11-30 04:59:57.000

...

    懂點(diǎn)網(wǎng)站建設(shè)和SQL的幾乎都知道用SQL進(jìn)行求和計(jì)算，SQL語(yǔ)句好寫(xiě)，但怎么讓結(jié)果顯示費(fèi)了點(diǎn)功夫，總結(jié)技巧如下：
    先看示例：
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

技巧補(bǔ)充

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

集合函數(shù)（類(lèi)似SUM）經(jīng)常需要用GROUP BY來(lái)進(jìn)行功能性的補(bǔ)充。

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中去是因?yàn)榧虾瘮?shù)(像SUM)每當(dāng)他們被訪問(wèn)時(shí)就會(huì)返回集合所有欄目的值，而且沒(méi)有GROUP BY的話就不能夠找出單獨(dú)一種欄目所累計(jì)的值了。

The syntax for the GROUP BY function is:

使用GROUP BY函數(shù)的語(yǔ)法為:

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:

返回的結(jié)果為:

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:

上面這些代碼幾乎是無(wú)效的，因?yàn)闄谀克祷氐臄?shù)值并不屬于我們想要的那種合計(jì)。使用 GROUP BY子句可以解決這個(gè)問(wèn)題：

SELECT Company,SUM(Amount) FROM Sales

GROUP BY Company

Returns this result:

返回的結(jié)果為：

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關(guān)鍵字在使用集合函數(shù)時(shí)不能使用，所以在集合函數(shù)中加上了HAVING來(lái)起到測(cè)試查詢(xún)結(jié)果是否符合條件的作用。

The syntax for the HAVING function is:

HAVING的使用語(yǔ)法為：

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語(yǔ)句：

SELECT Company,SUM(Amount) FROM Sales

GROUP BY Company

HAVING SUM(Amount)>10000

Returns this result

返回的結(jié)果為

Company	SUM(Amount)
W3Schools	12600

SQL的其它相關(guān)計(jì)算查詢(xún):

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
#################################################

上一條：網(wǎng)站建設(shè)-ASP時(shí)間函數(shù)詳解大全

下一條：ASP字符串函數(shù)大全