多级分组

• 多集分组

select a,b,count(*) from tab1,tab2

group by

 grouping sets(a,(a,b))

 having grouping(a)=1

 

Tip:

grouping(a)值为1时,代表统计的是a列,以除a以外的列分组

 

例1:

select a,b,count(*) as sum from tab1 group by a grouping sets(a,b);

=

select a,count(*) as sum from tab1 group by a ;

+

select b,count(*) as sum from tab1 group by b ;

 

例2:

 

--()增加总计

select a, b,count(*) from tbname group by grouping sets(a,(a,b),());
=
 select count(*) from tbname;

+
 select a,count(*) from tbname group by a;

+
 select a,b,count(*) from tbname group by a,b
 

 

• 增加总计

select value(char(a),'all'),

         value(char(b),'all b '),

count(*)

from tab1,tab2

where tab1.n=tab2.n

group by (char(a),(char(a),b),()); 

 

Tip:

value(a,b) 当a为空值时,返回b的值.用法与COALESCE相当,注意a与b的类型必须相同

• rollup

group by rollup(a,b,c) order by a,b,c 

 

 

rollup(a,b,c)=group by(a,b,c)+group by(a,b)+group by (a)+group by(全部)

 

• cube

group by cube(a,b,c) order by a,b,c

group by a,b,c with cube

 

cube(a,b,c)=group by(a,b,c)+group by(a,b)+group by(a,c)+group by(a)+group by(b,c)+group by(b)+group by(c)+group by(全部)

 

• 美化合计结果

select Decode(Grouping(a),1,'a统计',a) ,Decode(Grouping(b),1,'b统计',b) ,count(*) from tbname group by grouping sets(a,b)

 

Tip:

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)

ELSE
　　　　RETURN(缺省值)
END IF

 

评论

1 楼 sue 2012-03-19  

mysql> SELECT a,b,c,sum(d)
    -> FROM table
    -> GROUP BY a,b,c WITH ROLLUP