Oracle group statement inquiry (Note), oraclegroup

Oracle group statement inquiry (Note), oraclegroup

1. The group by statement does not have the sorting function in oracle. order by must be used to sort the statements according to the predefined results.

2. cube extension of group

1 with test as 2 (3 select 1 id, 2 name from dual 4) 5 select id, name from test group by cube (id, name ); 6 7. The output result is 8 id name 9 null null10 1 null11 null 212 1 2

 

It is not hard to see that the role of group by cube is to introduce null into a Cartesian Product and finally display it. In some cases, it is very convenient to use and can replace union all in some cases, high efficiency. When the data volume is large, only one empty column appears.

3. grouping () function

Grouping () is used with cube to determine whether this value is a null value generated by aggregation. If it is 1, it is not zero.

1 with test as 2 (3 select 1 id, 2 name from dual 4) 5 select id, name from test 6 group by cube (id, name) 7 having grouping (id) = 1; 8 9 the output result is 10 id name11 null null12 null 213 14 15 with test as16 (17 select 1 id, 2 name from dual18) 19 select id, name from test 20 group by cube (id, name) 21 having grouping (id) = 0; 22 23 output result is 24 id name25 1 null26 1 2

4. grouping_id () function

Grouping_id () is similar to grouping () to some extent. The difference is that grouping () calculates an expression and returns 0 or 1, while group_id () calculates an expression, determine which row of the parameter is used to generate a superaggregate row. Then, a vector is common and the value is returned as an integer value.

1 with test as 2 (3 select 1 id, 2 name from dual 4), 5 cuded as (6 select 7 grouping_id (id, name) gid, 8 to_char (grouping (id) id_1, 9 to_char (grouping (name) name_1, 10 decode (grouping (id), 1, 'Id 1') id_2, 11 decode (grouping (name), 1, 'name 2') name_212 from test 13 group by cude (id, name) 14) 15 select16 gid, id_1 | name_1 dn, id_2, name_217 from18 cuded; 19 20 results: 21 gid dn id_2 name_222 0 00 23 1 01 name 224 2 10 id 125 3 11 id 1 name 226