MySQL分类汇总(group by...with rollup)统计时，如何显示“总计”字段?

1、背景介绍

MySQL分类汇总(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','扬州');

问题描述：查询学生在各个城市人数分布情况，并在最后一行数据显示人数总计结果。在使用group by...with rollup语句时最后一行的字段显示为null，显得特别别扭。
查询语句：
SELECT ci.name AS '城市',
COUNT(ci.name) AS '人数'
FROM student AS st
JOIN city AS ci
ON st.`cityid`=ci.`id`
GROUP BY ci.name WITH ROLLUP;
结果：
 

现在要求将这个null字段显示为“总计”，查阅了网上资料也没找到相应的解决方案，不过就在我一筹莫展之际有一条博客引起我的注意，起内容如下：
 

使用grouping这个函数发现一直报错提示无法识别，仔细观察一下原来这是sql的语法，难道MySQL就实现不了了吗？后又查询了一些资料，发现网上很多人关于group by会总结果都是使用sum计算的。没办法只得自己想办法了，我首先想到的方法是使用带条件的子查询，输出到最后一条数据时显示“总计”字段，但又嫌太复杂没有写。后来按上文研究了一下case语句，觉得这个视乎可行，也行可以使用case判断为null是替换数据，于是写出了如下的代码：
查询语句：

SELECT case
when ci.name is null then '总计'
else ci.name
end AS '城市',
COUNT(ci.name) AS '人数'
FROM student AS st
JOIN city AS ci ON st.`cityid`=ci.`id`
GROUP BY ci.name WITH ROLLUP;
执行结果:
 

这难道不行吗？逻辑上面不存在错误啊，这下我吃惊了。难道是我记错了，判断字段为空不是 is null？难道用=？
 

很明显还是失败了。后来我又想到，我city字段定义的是varchar类型，别和我说用字符串就可以？
 

还是不行，索性查了一下判断字段为null的方法。=”、“<>”、“!=”、“>”、“>=”、“<”、“<=”等运算符都不能用来判断空值（NULL）。一旦使用，结果将返回NULL。如果要判断一个值是否为空值，可以使用“<=>”、IS NULL和IS NOT NULL来判断。于是乎使用了<=>.
查询语句：
SELECT case
when ci.name <=> null then '总计'
else ci.name
end AS '城市',
COUNT(ci.name) AS '人数'
FROM student AS st
JOIN city AS ci ON st.`cityid`=ci.`id`
GROUP BY ci.name WITH ROLLUP;
 

这下成功了，但又有疑问了<=>是判断等于null的意思，is null不也是吗？为什么只有<=>可以而is null就不行。索性我换了一种思路再试了一下：
查询语句：
SELECT case
when ci.name is not null then ci.name
else '总计'
end AS '城市',
COUNT(ci.name) AS '人数'
FROM student AS st
JOIN city AS ci ON st.`cityid`=ci.`id`
GROUP BY ci.name WITH ROLLUP;
结果：

此刻总计字段已经显示出来了，完美解决问题。