|
|
Posted on 2007-05-04 11:52 大大毛 阅读(328) 评论(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
這樣即可實現最終的效果了。
|