利用Mysql的with rollup的到汇总信息

1、介绍

   1.1 WITH ROLLUP 是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息；
   rollup 可以实现多维度的聚合（相当于oracle的高级分组函数rollup（a，b，c,...））
   对分组的列，以（a，b，c）为例，会实现（a，b，c）,(a,b，null),(a，null，null),(null，null，null)的聚合方式
   也就是说它会对具有n个维度的列，实现n+1次分组。

   1.2 语法：group by...with rollup

2、测试用例：

  1）.学生信息表

CREATE TABLE student(

sid VARCHAR(16) PRIMARY KEY NOT NULL COMMENT '学生学号',

class VARCHAR(16) NOT NULL COMMENT '班级',

profession VARCHAR(32) NOT NULL COMMENT '专业',

NAME VARCHAR(8) NOT NULL COMMENT '学生姓名',

phone VARCHAR(11) NULL COMMENT '手机',

sex TINYINT(1) NULL COMMENT '性别',

cityid VARCHAR(32) NULL COMMENT '城市编号' );

插入的数据

INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130327','3','jsj','徐郎','13814968532',1,'nj'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130427','4','jsj','王萌','13114968532',0,'yc'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130527','5','jsj','大头','13312968532',0,'nt'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130301','3','jsj','赤壁剑','13817968532',0,'nt');

INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130302','3','jsj','李颖','13314068532',0,'xz'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130303','3','jsj','刘亦菲','13814368532',0,'zj');

INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130304','3','jsj','范冰冰','13814860532',0,'yz');

INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130305','3','jsj','样样','13814968132',1,'yc'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130306','3','jsj','郑爽','13214968332',1,'xz'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130407','4','jsj','小乔','13714068532',1,'zj'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130401','4','jsj','周瑜','12814968532',0,'nj'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130501','5','jsj','老葛','13314968532',1,'yz'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130508','5','jsj','强哥','13614968532',1,'yz'); INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1305130508','5','english','允儿','13810968532',1,'nt');

INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1205130508','5','math','张艺馨','13814918532',0,'nj'); -------------------------------------------------------------------------------------------------------------------------------------------------------------------------  2）.城市表

CREATE TABLE city(

id VARCHAR(32) PRIMARY KEY NOT NULL COMMENT '城市编号',

NAME VARCHAR(32) NOT NULL COMMENT '城市名称' );

插入的数据

INSERT INTO city(id,NAME) VALUES('nj','南京');

INSERT INTO city(id,NAME) VALUES('yc','盐城');

INSERT INTO city(id,NAME) VALUES('nt','南通'); INSERT INTO city(id,NAME) VALUES('xz','徐州');

INSERT INTO city(id,NAME) VALUES('zj','镇江');

INSERT INTO city(id,NAME) VALUES('yz','扬州');

3.问题描述：

    查询学生在各个城市人数分布情况，并在最后一行数据显示人数总计结果；

   sql：

    select ifnull(t2.name,'总计'),count(t1.name)
    from student t1 join city t2
    on t1.cityid=t2.id
    group by t2.name with rollup

  查询结果：

多维度聚合查看每年员工的客户，客户订单数量的信息。

select  empid,custid,year(orderdate) year,sum(qty)
   from orders
   group by empid,custid,year(orderdate)  
   with ROLLUP 

4.扩展用rollp实现cube

mysql 暂不支持cube，（oracle有cube函数），cube在分组时。如果有n个维度，会产生2的n次方个聚合。

例（a，b，c）形成的分组有 (a,b,c),(a,c),(a,c),(b,c),(a),(b),(c),(null)

我们可以采用rollup 对它进行模拟。

即：

 select empid,custid,year(orderdate) year,sum(qty) sum
    from orders
    group by empid,custid,year(orderdate) with ROLLUP
    #相当于abc,ab,a,null
    union

    select empid,custid,year(orderdate) year,sum(qty) sum
    from orders
    group by empid,year(orderdate),custid with ROLLUP
    #相当于ac
    union

    select empid,custid,year(orderdate) year,sum(qty) sum
    from orders
    group by custid,year(orderdate),empid with ROLLUP
    #相当于bc,b
    union

    select empid,custid,year(orderdate) year,sum(qty) sum
    from orders
    group by year(orderdate),custid,empid with ROLLUP
    #相当于 c