作者ppanerai (全職業封頂路途漫長)
看板Database
標題[SQL ] ROLLUP
時間Mon Jun 26 18:00:53 2006
下班前偷偷一貼
以下Sample是由 Oracle 的文件貼出來的
其他的DB ... 嗯 ... 就煩勞各位大大試一下嚕
( >"< ... 我只用過Oracle咩)
Rollup
讓 select statement 可以得出小計與總和
rollup 算是 group by 的延伸
group by 相信大家用得很多也很熟悉
會用 group by , 應該就會使用 rollup
rollup 對原本的 Query 造成的 performance 影響也不大
Syntax
SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)
直接講例子應該比較容易理解
SELECT Time, Region, Department,
SUM(Profit) AS Profit
FROM sales
GROUP BY ROLLUP(Time, Region, Dept);
Time Region Department Profit
---- ------ ---------- ------
1996 Central VideoRental 75,000
1996 Central VideoSales 74,000
1996 Central NULL 149,000 <----- 小計 (Time, Region - 1996,Central)
1996 East VideoRental 89,000
1996 East VideoSales 115,000
1996 East NULL 204,000 <----- 小計 (Time, Region - 1996,East)
1996 West VideoRental 87,000
1996 West VideoSales 86,000
1996 West NULL 173,000 <----- 小計 (Time, Region 1996, West)
1996 NULL NULL 526,000 <----- 小計 (Time - 1996)
1997 Central VideoRental 82,000
1997 Central VideoSales 85,000
1997 Central NULL 167,000 <----- 小計 (Time, Region - 1997,Central)
1997 East VideoRental 101,000
1997 East VideoSales 137,000
1997 East NULL 238,000 <----- 小計 (Time, Region - 1997,East)
1997 West VideoRental 96,000
1997 West VideoSales 97,000
1997 West NULL 193,000 <----- 小計 (Time, Region - 1997,West)
1997 NULL NULL 598,000 <----- 小計 (Time 1997 )
NULL NULL NULL 1,124,000 <----- 小計 (TOTAL)
( NULL 在Oracle 的Output 會是一個 Blank ,這邊標為 NULL 是為了比較好辨識 )
如果改成以下這樣稱為 Partial ROLLUP
這樣一來就不會出現 Total (上面的最後一行)
SELECT Time, Region, Department,
SUM(Profit) AS Profit FROM sales
GROUP BY Time, ROLLUP(Region, Dept);
Time Region Department Profit
---- ------ ---------- ------
1996 Central VideoRental 75,000
1996 Central VideoSales 74,000
1996 Central NULL 149,000
1996 East VideoRental 89,000
1996 East VideoSales 115,000
1996 East NULL 204,000
1996 West VideoRental 87,000
1996 West VideoSales 86,000
1996 West NULL 173,000
1996 NULL NULL 526,000
1997 Central VideoRental 82,000
1997 Central VideoSales 85,000
1997 Central NULL 167,000
1997 East VideoRental 101,000
1997 East VideoSales 137,000
1997 East NULL 238,000
1997 West VideoRental 96,000
1997 West VideoSales 97,000
1997 West NULL 193,000
1997 NULL NULL 598,000
所以可以看出來
包在 rollup 裡面的欄位,
會被拿來當做group的欄位 (- -講不清楚)
大家只要找幾個例子試一試就知道了
一樣地 如果不使用 rollup ,
就會變成 ... = =
SELECT Time, Region, Department, SUM(Profit)
FROM Sales
GROUP BY Time, Region, Department
UNION ALL
SELECT Time, Region, '' , SUM(Profit)
FROM Sales
GROUP BY Time, Region
UNION ALL
SELECT Time, '', '', SUM(Profits)
FROM Sales
GROUP BY Time
UNION ALL
SELECT '', '', '', SUM(Profits)
FROM Sales;
感謝各位撥冗閱讀 ...
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 202.145.230.2