目前在开发过程中,已经能够实现通过rollup对分组数据进行小计合计项目的显示输出,但是,我们在实际输出需要小计项目在各分组项目的上方显示,不知道应该在sql语句中进行怎样的描述呢?谢谢大家给点意见和思路,谢谢!
解决方案 »
- 触发器中如何撤销插入!
- 请问一个特殊的建表语句SQL如何写?
- PL/SQL块中为什么定义varchar2变量必须加长度
- 调用过程中发现变量无法赋值,请指教
- 深圳发展银行面试数据库去过进来谈谈啊
- 大家快来救救我!oracle8.1.7数据库导入问题!
- 一次更新七万条数据得多长时间?
- 一道考研题目:DBMS的分类
- 如何插入sql语句到数据表中,怎么对sql语句中的单引号转义?
- ORA-02049: timeout: distributed transaction waiting for lock
- Oracle无驱动连接工具,不需安装Oracle客户端,连接oracle,仅500K
- 获得当前时间并转换成字符串,但是不能精确到毫秒?
The GROUPING function distinguishes super aggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP and CUBE produce super aggregate rows where the set of all values is represented by null. Using the GROUPING function, you can distinguish a null representing the set of all values in a super aggregate row from a null in a regular row.
The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The datatype of the value returned by the GROUPING function is Oracle NUMBER.
Syntax
>ÄGROUPINGÄÄ(expr)ÄÄ><
Examples
In the following example, which uses the sample tables hr.departments and hr.employees, if the GROUPING function returns 1 (indicating a super aggregate row rather than a regular row from the table), the string "All Jobs" appears in the "JOB" column instead of the null that would otherwise appear:SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
department_name) AS department,
DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id);
DEPARTMENT JOB Total Empl Average Sal
------------------------------ ---------- ---------- -----------
Accounting AC_ACCOUNT 1 99600
Accounting AC_MGR 1 144000
Accounting All Jobs 2 121800
Administration AD_ASST 1 52800
Administration All Jobs 1 52800
Executive AD_PRES 1 288000
Executive AD_VP 2 204000
Executive All Jobs 3 232000
Finance FI_ACCOUNT 5 95040
Finance FI_MGR 1 144000
Finance All Jobs 6 103200
.
.
.
This example uses GROUPING to create a set of mask columns for the result set shown in Output 15-3. The mask columns are easy to analyze programmatically.SELECT Time, Region, Department, SUM(Profit) AS Profit,
GROUPING (Time) as T,
GROUPING (Region) as R,
GROUPING (Department) as D
FROM Sales
GROUP BY ROLLUP (Time, Region, Department);
Output 1 shows the results of this query.Output 1Use of GROUPING Function:Time Region Department Profit T R D
---- ------ ---------- ------ - - -
1996 Central VideoRental 75,000 0 0 0
1996 Central VideoSales 74,000 0 0 0
1996 Central NULL 149,000 0 0 1
1996 East VideoRental 89,000 0 0 0
1996 East VideoSales 115,000 0 0 0
1996 East NULL 204,000 0 0 1
1996 West VideoRental 87,000 0 0 0
1996 West VideoSales 86,000 0 0 0
1996 West NULL 173,000 0 0 1
1996 NULL NULL 526,000 0 1 1
1997 Central VideoRental 82,000 0 0 0
1997 Central VideoSales 85,000 0 0 0
1997 Central NULL 167,000 0 0 1
1997 East VideoRental 101,000 0 0 0
1997 East VideoSales 137,000 0 0 0
1997 East NULL 238,000 0 0 1
1997 West VideoRental 96,000 0 0 0
1997 West VideoSales 97,000 0 0 0
1997 West NULL 193,000 0 0 1
1997 NULL VideoRental 598,000 0 1 1
NULL NULL NULL 1,124,000 1 1 1
A program can easily identify the detail rows above by a mask of "0 0 0" on the T, R, and D columns. The first level subtotal rows have a mask of "0 0 1", the second level subtotal rows have a mask of "0 1 1", and the overall total row has a mask of "1 1 1".Output 2 shows an ambiguous result set created using the CUBE extension.Output 2