[SQL ] ROLLUP

看板Database (資料庫)作者ppanerai (全職業封頂路途漫長)時間18年前 (2006/06/26 17:58)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串1/1

下班前偷偷一貼以下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