可以参考http://www.oracle.com.cn/onlinedoc/server.920/a96520/aggreg.htm#12067以下为部分摘录GROUPING Functions Two challenges arise with the use of ROLLUP and CUBE. First, how can you programmatically determine which result set rows are subtotals, and how do you find the exact level of aggregation for a given subtotal? You often need to use subtotals in calculations such as percent-of-totals, so you need an easy way to determine which rows are the subtotals. Second, what happens if query results contain both stored NULL values and "NULL" values created by a ROLLUP or CUBE? How can you differentiate between the two?See Also: Oracle9i SQL Reference for syntax and restrictions GROUPING Function GROUPING handles these problems. Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.GROUPING Syntax GROUPING appears in the selection list portion of a SELECT statement. Its form is:SELECT ... [GROUPING(dimension_column)...] ... GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} (dimension_column)Example 18-6 GROUPING to Mask Columns This example uses GROUPING to create a set of mask columns for the result set shown in Example 18-3. The mask columns are easy to analyze programmatically.SELECT channel_desc, calendar_month_desc, country_id, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$, GROUPING(channel_desc) as Ch, GROUPING(calendar_month_desc) AS Mo, GROUPING(country_id) AS Co FROM sales, customers, times, channels WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND sales.channel_id= channels.channel_id AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc IN ('2000-09', '2000-10') AND country_id IN ('UK', 'US') GROUP BY ROLLUP(channel_desc, calendar_month_desc, country_id);CHANNEL_DESC CALENDAR CO SALES$ CH MO CO -------------------- -------- -- -------------- --------- --------- --------- Direct Sales 2000-09 UK 1,378,126 0 0 0 Direct Sales 2000-09 US 2,835,557 0 0 0 Direct Sales 2000-09 4,213,683 0 0 1 Direct Sales 2000-10 UK 1,388,051 0 0 0 Direct Sales 2000-10 US 2,908,706 0 0 0 Direct Sales 2000-10 4,296,757 0 0 1 Direct Sales 8,510,440 0 1 1 Internet 2000-09 UK 911,739 0 0 0 Internet 2000-09 US 1,732,240 0 0 0 Internet 2000-09 2,643,979 0 0 1 Internet 2000-10 UK 876,571 0 0 0 Internet 2000-10 US 1,893,753 0 0 0 Internet 2000-10 2,770,324 0 0 1 Internet 5,414,303 0 1 1 13,924,743 1 1 1
楼主所说的应该是grouping_id function 具体的也可以参考我的连接
oracle 里怎么使用group by 呀 select * from admin_user_group group by group_id 执行后总报错 在mysql里一切正常
Two challenges arise with the use of ROLLUP and CUBE. First, how can you programmatically determine which result set rows are subtotals, and how do you find the exact level of aggregation for a given subtotal? You often need to use subtotals in calculations such as percent-of-totals, so you need an easy way to determine which rows are the subtotals. Second, what happens if query results contain both stored NULL values and "NULL" values created by a ROLLUP or CUBE? How can you differentiate between the two?See Also:
Oracle9i SQL Reference for syntax and restrictions
GROUPING Function
GROUPING handles these problems. Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.GROUPING Syntax
GROUPING appears in the selection list portion of a SELECT statement. Its form is:SELECT ... [GROUPING(dimension_column)...] ...
GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} (dimension_column)Example 18-6 GROUPING to Mask Columns
This example uses GROUPING to create a set of mask columns for the result set shown in Example 18-3. The mask columns are easy to analyze programmatically.SELECT channel_desc, calendar_month_desc, country_id,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
GROUPING(channel_desc) as Ch,
GROUPING(calendar_month_desc) AS Mo,
GROUPING(country_id) AS Co
FROM sales, customers, times, channels
WHERE sales.time_id=times.time_id AND
sales.cust_id=customers.cust_id AND
sales.channel_id= channels.channel_id AND
channels.channel_desc IN ('Direct Sales', 'Internet') AND
times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_id IN ('UK', 'US')
GROUP BY ROLLUP(channel_desc, calendar_month_desc, country_id);CHANNEL_DESC CALENDAR CO SALES$ CH MO CO
-------------------- -------- -- -------------- --------- --------- ---------
Direct Sales 2000-09 UK 1,378,126 0 0 0
Direct Sales 2000-09 US 2,835,557 0 0 0
Direct Sales 2000-09 4,213,683 0 0 1
Direct Sales 2000-10 UK 1,388,051 0 0 0
Direct Sales 2000-10 US 2,908,706 0 0 0
Direct Sales 2000-10 4,296,757 0 0 1
Direct Sales 8,510,440 0 1 1
Internet 2000-09 UK 911,739 0 0 0
Internet 2000-09 US 1,732,240 0 0 0
Internet 2000-09 2,643,979 0 0 1
Internet 2000-10 UK 876,571 0 0 0
Internet 2000-10 US 1,893,753 0 0 0
Internet 2000-10 2,770,324 0 0 1
Internet 5,414,303 0 1 1
13,924,743 1 1 1
select * from admin_user_group
group by group_id
执行后总报错
在mysql里一切正常