java典型應(yīng)用徹查1000例第六卷web開發(fā)源碼習(xí)題bookjavavol-6webch

1、Chapter 21聚合函數(shù) 聚合函數(shù)(Aggregate Functions)是將多個(gè)輸入值(Input Value) 經(jīng)過計(jì)算后返回(Return)一個(gè)結(jié)果值(Result Value)。當(dāng)聚合式執(zhí)行多組聚合運(yùn)算時(shí)，稱為組聚合(Group aggregation)。SQL語言內(nèi)置5個(gè)聚合函數(shù)命令：（1）平均值(Average)命令A(yù)VG；（2）總和值(Total) 命令SUM；（3）計(jì)數(shù)值(Count)命令COUNT；（4）最大值(Maximum)命令MAX；（5）最小值(Minimum)命令MIN。上述函數(shù)命令中，AVG、SUM必須用于數(shù)字(Number)環(huán)境，其他命令也可用于如字符串(

2、String)環(huán)境之類的其他環(huán)境。 21-3 平均值 SQL語言提供命令A(yù)VG計(jì)算特定字段(Field)各元組(Tuples)的平均值(Average Value)。設(shè)計(jì)SQL命令：SELECT AVG(assets) AS avg_assets FROM Branch21-4 總和值SQL語言提供命令SUM計(jì)算特定字段各元組的總和值(Total Sum Value)。設(shè)計(jì)SQL命令：SELECT SUM(amount) AS total_amount FROM BorrowSELECT branch_name, SUM(amount) AS group_amount FROM Borrow

3、GROUP BY branch_nameSELECT branch_name, SUM(amount) AS group_amount FROM Borrow GROUP BY branch_name HAVING SUM(amount)300021-5 計(jì)數(shù)值 SQL語言提供命令COUNT計(jì)算特定字段各組數(shù)據(jù)元素(Elements)的數(shù)量(Number)。設(shè)計(jì)SQL命令：SELECT branch_name, COUNT(customer_name) AS customer_count FROM Borrow GROUP BY branch_nameSELECT branch_name, S

4、UM(amount) AS group_amount FROM Borrow GROUP BY branch_name HAVING SUM(amount)3000 AND COUNT(customer_name)=2SELECT Borrow.branch_name, COUNT(Borrow.customer_name) AS customer_count FROM Borrow, Deposit WHERE Borrow.branch_name = Deposit.branch_name GROUP BY Borrow.branch_name HAVING COUNT(Borrow.cu

5、stomer_name)=221-6 最大值 SQL語言提供命令MAX搜索特定字段各組數(shù)據(jù)的最大值(Maximum Value)。 設(shè)計(jì)SQL命令：SELECT MAX(amount) AS max_amount FROM BorrowSELECT customer_name, amount FROM Borrow WHERE amount = MAX(amount) 討論事項(xiàng): 對(duì)于聚合運(yùn)算，條件子句WHERE或HAVING均無法在Access中運(yùn)行，通常都是以嵌套子句進(jìn)行替換來運(yùn)行。故本例WHERE子句中的聚合命令MAX以22-3節(jié)范例105的嵌套子句進(jìn)行替換來運(yùn)行（步驟2的命令仍適用于其他大型數(shù)據(jù)庫）。21-7 最小值SQL語言提供命令MIN搜索特定字段各組數(shù)據(jù)的最小值(Minimum Value) 。設(shè)計(jì)SQL命令：SELECT MIN(amount) AS min_amount FROM BorrowSELECT customer_name, amountFROM BorrowWHERE amount = MIN(amount)討論事項(xiàng): 條件子句WHERE或HAVING均無法在