...

    懂點網(wǎng)站建設(shè)和SQL的幾乎都知道用SQL進(jìn)行求和計算，SQL語句好寫，但怎么讓結(jié)果顯示費了點功夫，總結(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

技巧補充

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

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

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中去是因為集合函數(shù)(像SUM)每當(dāng)他們被訪問時就會返回集合所有欄目的值，而且沒有GROUP BY的話就不能夠找出單獨一種欄目所累計的值了。

The syntax for the GROUP BY function is:

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

GROUP BY Example

舉例

This "Sales" Table:

這是張名為"Sales"的表:

And This SQL:

這是條SQL:

Returns this result:

返回的結(jié)果為:

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

上面這些代碼幾乎是無效的，因為欄目所返回的數(shù)值并不屬于我們想要的那種合計。使用 GROUP BY子句可以解決這個問題：

Returns this result:

返回的結(jié)果為：

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ù)中加上了HAVING來起到測試查詢結(jié)果是否符合條件的作用。

The syntax for the HAVING function is:

HAVING的使用語法為：

This "Sales" Table:

這是名為"Sales"的表：

This SQL:

SQL語句：

Returns this result

返回的結(jié)果為

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

.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ù)詳解大全

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