GROUP_ID，GROUPING，GROUPING_ID使用例子介绍

（参考ORACLE文档）

GROUP_ID函数使用

GROUP_ID distinguishes duplicate groups resulting from a GROUP BY specification. It is useful in filtering out duplicate groupings from the query result. It returns an Oracle NUMBER to uniquely identify duplicate groups. This function is applicable only in a SELECT statement that contains a GROUP BY clause.

If n duplicates exist for a particular grouping, then GROUP_ID returns numbers in the range 0 to n-1.

GROUP_ID函数能够区分GROUP BY表达式所产生的复制组。从查询结果中过滤复制组时显得特别有用。

它返回一个ORACLE NUMBER类型的数值来唯一标示每一个复制组。这个函数只是在包含GROUP BY 从句的select语句中使用。

Examples

The following example assigns the value 1 to the duplicate co.country_region grouping from a query on the sample tables sh.countries and sh.sales:

SELECT co.country_region, co.country_subregion,

SUM(s.amount_sold) "Revenue",

GROUP_ID() g

FROM sales s, customers c, countries co

WHERE s.cust_id = c.cust_id AND

c.country_id = co.country_id AND

s.time_id = '1-JAN-00' AND

co.country_region IN ('Americas', 'Europe')

GROUP BY co.country_region,

ROLLUP (co.country_region, co.country_subregion);

COUNTRY_REGION COUNTRY_SUBREGION Revenue G

-------------------- -------------------- ---------- ----------

Americas Northern America 220844 0

Americas Southern America 10872 0

Europe Eastern Europe 12751 0

Europe Western Europe 558686 0

Americas 231716 0

Europe 571437 0

Americas 231716 1

Europe 571437 1

To ensure that only rows with GROUP_ID < 1 are returned, add the following HAVING clause to the end of the statement :

HAVING GROUP_ID() < 1

Grouping函数使用：

GROUPING distinguishes superaggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP and CUBE produce superaggregate 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 superaggregate 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. Please refer to the SELECT group_by_clause for a discussion of these terms.

Examples

In the following example, which uses the sample tables hr.departments and hr.employees, if the GROUPING function returns 1 (indicating a superaggregate row rather than a regular row from the table), then 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

GROUPING_ID函数使用：

GROUPING_ID returns a number corresponding to the GROUPING bit vector associated with a row. GROUPING_ID is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE, and a GROUPING function. In queries with many GROUP BY expressions, determining the GROUP BY level of a particular row requires many GROUPING functions, which leads to cumbersome SQL. GROUPING_ID is useful in these cases.

GROUPING_ID is functionally equivalent to taking the results of multiple GROUPING functions and concatenating them into a bit vector (a string of ones and zeros). By using GROUPING_ID you can avoid the need for multiple GROUPING functions and make row filtering conditions easier to express. Row filtering is easier with GROUPING_ID because the desired rows can be identified with a single condition of GROUPING_ID = n. The function is especially useful when storing multiple levels of aggregation in a single table.

Examples

The following example shows how to extract grouping IDs from a query of the sample table sh.sales:

SELECT channel_id, promo_id, sum(amount_sold) s_sales,

GROUPING(channel_id) gc,

GROUPING(promo_id) gp,

GROUPING_ID(channel_id, promo_id) gcp,

GROUPING_ID(promo_id, channel_id) gpc

FROM sales

WHERE promo_id > 496

GROUP BY CUBE(channel_id, promo_id);

C PROMO_ID S_SALES GC GP GCP GPC

- ---------- ---------- ---------- ---------- ---------- ----------

C 497 26094.35 0 0 0 0

C 498 22272.4 0 0 0 0

C 499 19616.8 0 0 0 0

C 9999 87781668 0 0 0 0

C 87849651.6 0 1 1 2

I 497 50325.8 0 0 0 0

I 498 52215.4 0 0 0 0

I 499 58445.85 0 0 0 0

I 9999 169497409 0 0 0 0

I 169658396 0 1 1 2

P 497 31141.75 0 0 0 0

P 498 46942.8 0 0 0 0

P 499 24156 0 0 0 0

P 9999 70890248 0 0 0 0

P 70992488.6 0 1 1 2

S 497 110629.75 0 0 0 0

S 498 82937.25 0 0 0 0

S 499 80999.15 0 0 0 0

S 9999 267205791 0 0 0 0

S 267480357 0 1 1 2

T 497 8319.6 0 0 0 0

T 498 5347.65 0 0 0 0

T 499 19781 0 0 0 0

T 9999 28095689 0 0 0 0

T 28129137.3 0 1 1 2

497 226511.25 1 0 2 1

498 209715.5 1 0 2 1

499 202998.8 1 0 2 1

9999 623470805 1 0 2 1

624110031 1 1 3 3

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/36779/viewspace-896287/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/36779/viewspace-896287/