作者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