|
|
Posted on 2007-05-04 11:52 大大毛 閱讀(344) 評論(0) 編輯 收藏 所屬分類: SQL
問題：
??????分類小計的情況很多，通常情況下我們可能會在SQL的外部來實現分類小計，但是相比直接使用SQL得到滿意的結果集來說效率上就會差太多，現在就來解決它好了。先看表結構：
?
表結構及示例數據
--
類別
Create
?
Table
?tbCategory?(
????cID?
int
?
primary
?
key
,
????cName?
varchar
(
50
)
);
--
出版物的類型
Create
?
Table
?tbType?(
????tID?
int
?
primary
?
key
,
????tName?
varchar
(
50
)
);
--
書
Create
?
Table
?tbBook?(
????bID?
int
?
primary
?
key
,
????cID?
int
,
????tID?
int
,
????bName?
varchar
(
50
),
????price?
money
);
Insert
?
Into
?tbCategory?
Values
?(
1
,
'
生物
'
);
Insert
?
Into
?tbCategory?
Values
?(
2
,
'
地理
'
);
Insert
?
Into
?tbCategory?
Values
?(
3
,
'
計算機
'
);
Insert
?
Into
?tbType?
Values
?(
1
,
'
印刷品
'
);
Insert
?
Into
?tbType?
Values
?(
2
,
'
電子書
'
);
Insert
?
Into
?tbBook?
Values
?(
1
,
1
,
1
,
'
昆蟲學
'
,
23.5
);
Insert
?
Into
?tbBook?
Values
?(
2
,
1
,
1
,
'
海洋生物探索
'
,
89.9
);
Insert
?
Into
?tbBook?
Values
?(
3
,
1
,
2
,
'
昆蟲學
'
,
5
);
Insert
?
Into
?tbBook?
Values
?(
4
,
2
,
1
,
'
中國地理
'
,
51
);
Insert
?
Into
?tbBook?
Values
?(
5
,
2
,
1
,
'
世界地理
'
,
85
);
Insert
?
Into
?tbBook?
Values
?(
6
,
2
,
2
,
'
中國地理
'
,
6.8
);
Insert
?
Into
?tbBook?
Values
?(
7
,
2
,
2
,
'
地理圖冊
'
,
15
);
Insert
?
Into
?tbBook?
Values
?(
8
,
3
,
1
,
'
JAVA
'
,
105
);
Insert
?
Into
?tbBook?
Values
?(
9
,
3
,
1
,
'
NET編輯
'
,
95
);
Insert
?
Into
?tbBook?
Values
?(
10
,
3
,
1
,
'
1小時精通JAVA
'
,
35
);
??????可以看到這3個層表現出的是一個3層的結構，tbCategory 是第一層代表大的類別 tbType 是第二層代表出版物的類型 tbBook 是最下一層，現在我們想要實現分類匯總，需要實現下面的效果：
效果表：
|
名稱
|
金額
|
數量
|
|
生物
|
118.4
|
3
|
|
??印刷品
|
113.4
|
2
|
|
????昆蟲學
|
23.5
|
1
|
|
????海洋生物探索
|
89.9
|
1
|
|
??電子書
|
5
|
1
|
|
????昆蟲學
|
5
|
1
|
??????從這張效果表可以看到，我們需要對數據分類匯總才能實現如上表的效果。
解決：
??????平時如果只是2層的關係，直接在SQL語句中實現 Group By 關鍵字即可以實現，但是現在的3層呢？答案是相同的，只是需要將2次匯總的結果集 Union 就好，下面將步驟分解來看：
??????1. 我們先對類別實施匯總
Select
????????cID,
Sum
(price)?
as
?price,
Count
(
*
)?
as
?bookCount
????
From
?
????????tbBook
????
Group
?
By
????????cID
??????2. 我們再對出版物的類別匯總
Select
????????cID,tID,
Sum
(price)?
as
?price,
Count
(
*
)?
as
?bookCount
????
From
????????tbBook
????
Group
?
By
????????cID,tID
??????3. 將上面兩條 SQL Union
Select
????????cID,
''
?
as
?tID,
Sum
(price)?
as
?price,
Count
(
*
)?
as
?bookCount
????
From
?
????????tbBook
????
Group
?
By
????????cID
Union
Select
????????cID,tID,
Sum
(price)?
as
?price,
Count
(
*
)?
as
?bookCount
????
From
????????tbBook
????
Group
?
By
????????cID,tID
Order
?
By
????cID,tID
??????出來的結果是這樣：
|
cID
|
tID
|
price
|
bookCount
|
|
1
|
0
|
118.4
|
3
|
|
1
|
1
|
113.4
|
2
|
|
1
|
2
|
5
|
1
|
|
2
|
0
|
157.8
|
4
|
|
2
|
1
|
136
|
2
|
|
2
|
2
|
21.8
|
2
|
|
3
|
0
|
235.5
|
3
|
|
3
|
1
|
235.5
|
3
|
??????4. 代入類別名稱和出版物類型的名稱並 Union tbBook表的記錄，得到最終的結果集，為了能夠達到排序的效果，我們會在這一步插入一個標識列，用它來控制顯示的順序：
Select
?
'
0
'
?
as
?flag,tbBook.cID?
as
?cID,
min
(tbBook.tID)?
as
?tID,
min
(tbCategory.cName)?
as
?cName,
Sum
(price)?
as
?price,
Count
(
*
)?
as
?bookCount?
From
?tbBook?
Join
?tbCategory?
On
?tbBook.cID
=
tbCategory.cID?
Group
?
By
?tbBook.cID
union
Select
?
'
1
'
,tbBook.cID,tbBook.tID,
min
(tbType.tName),
Sum
(price)?
as
?price,
Count
(
*
)?
From
?tbBook?
Join
?tbType?
On
?tbBook.tID
=
tbType.tID??
Group
?
By
?tbBook.cID,tbBook.tID
union
Select
?
'
2
'
,cID,tID,bName,price,
1
?
From
?tbBook
Order
?
By
?cID,tID,Flag
??????這樣即可實現最終的效果了。
|