MySQL关键字with rollup的用法

关键字with rollup用于group by后，它可以将group by分组后的结果进行分类汇总。

下面用具体示例来说明with rollup的用法：

# 创建示例要用到的学生成绩表grade：

create table grade(
sid char(2) not null default '00',
cid char(2) not null default '00',
sname varchar(5) not null default '',
score int not null default 0
)engine myisam charset utf8;

# 成绩表字段说明：
# sid表示学生编号，cid表示课程编号，sname表示学生姓名，score表示学生分数。

# 向成绩表grade中插入数据：

insert into grade values
('01','01','张三',85),
('02','02','李四',89),
('03','03','王五',78),
('04','04','周六',91),
('05','01','张三',99),
('06','02','李四',77),
('07','03','王五',68),
('08','01','张衡',100),
('09','02','孙五',99),
('10','03','李雷',73),
('11','01','张三',84),
('12','02','赵健',59),
('13','03','李雷',67),
('14','04','田七',100);

示例1：想要查询grade表中不同姓名下的学生人数信息。

select sname,count(*) as num_st from grade group by sname;

# 返回结果为：
+-------+--------+
| sname | num_st |
+-------+--------+
| 周六  |      1 |
| 孙五  |      1 |
| 张三  |      3 |
| 张衡  |      1 |
| 李四  |      2 |
| 李雷  |      2 |
| 王五  |      2 |
| 田七  |      1 |
| 赵健  |      1 |
+-------+--------+

# 如果想要在返回结果中增加一行总计行，则可以在group by语句的后面跟上with rollup关键字：

select ifnull(sname,'总计') as sname,count(*) as num_st from grade group by sname with rollup;
# 返回结果为：
+-------+--------+
| sname | num_st |
+-------+--------+
| 周六  |      1 |
| 孙五  |      1 |
| 张三  |      3 |
| 张衡  |      1 |
| 李四  |      2 |
| 李雷  |      2 |
| 王五  |      2 |
| 田七  |      1 |
| 赵健  |      1 |
| 总计  |     14 |
+-------+--------+

示例2：想要查询grade表中不同课程id下的学生人数信息。

select cid,count(*) as num_st from grade group by cid;

# 返回结果为：
+-----+--------+
| cid | num_st |
+-----+--------+
| 01  |      4 |
| 02  |      4 |
| 03  |      4 |
| 04  |      2 |
+-----+--------+

# 同理，假如想要在返回结果中增加一行总计行，可以使用with rollup关键字：

select ifnull(cid,'总计') as cid,count(*) as num_st from grade group by cid with rollup;

# 返回结果为：
+------+--------+
| cid  | num_st |
+------+--------+
| 01   |      4 |
| 02   |      4 |
| 03   |      4 |
| 04   |      2 |
| 总计 |     14 |
+------+--------+

示例3：想要查询不同课程下，不同姓名的学生人数信息。

select cid,sname,count(*) as num_st from grade group by cid,sname;

# 返回结果为：
+-----+-------+--------+
| cid | sname | num_st |
+-----+-------+--------+
| 01  | 张三  |      3 |
| 01  | 张衡  |      1 |
| 02  | 孙五  |      1 |
| 02  | 李四  |      2 |
| 02  | 赵健  |      1 |
| 03  | 李雷  |      2 |
| 03  | 王五  |      2 |
| 04  | 周六  |      1 |
| 04  | 田七  |      1 |
+-----+-------+--------+

# 如果想要对返回结果中不同课程下的学生人数进行汇总，且增加一行总计行，则需要用到with rollup关键字：
select ifnull(cid,'总计') as cid,ifnull(sname,'小计') as sname,count(*) as num_st from grade 
group by cid,sname with rollup;

# 返回结果为：
+------+-------+--------+
| cid  | sname | num_st |
+------+-------+--------+
| 01   | 张三  |      3 |
| 01   | 张衡  |      1 |
| 01   | 小计  |      4 |
| 02   | 孙五  |      1 |
| 02   | 李四  |      2 |
| 02   | 赵健  |      1 |
| 02   | 小计  |      4 |
| 03   | 李雷  |      2 |
| 03   | 王五  |      2 |
| 03   | 小计  |      4 |
| 04   | 周六  |      1 |
| 04   | 田七  |      1 |
| 04   | 小计  |      2 |
| 总计 | 小计  |     14 |
+------+-------+--------+

PS：本文为博主原创文章，转载请注明出处。