MySQL分类汇总（withrollup）统计时，如何显
representer⽰“总计”字段？
测试⽤例：
-- 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','徐郎','138********',1,'nj');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130427','4','jsj','王萌','131********',0,'yc');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130527','5','jsj','⼤头','133********',0,'nt');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130301','3','jsj','⾚壁剑','138********',0,'nt');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130302','3','jsj','李颖','133********',0,'xz');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130303','3','jsj','刘亦菲','138********',0,'zj');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130304','3','jsj','范冰冰','138********',0,'yz');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130305','3','jsj','样样','138********',1,'yc');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130306','3','jsj','郑爽','132********',1,'xz');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130407','4','jsj','⼩乔','137********',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','⽼葛','133********',1,'yz');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1405130508','5','jsj','强哥','136********',1,'yz');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1305130508','5','english','允⼉','138********',1,'nt');
INSERT INTO student(sid, class, profession,NAME,phone,sex,cityid) VALUES('1205130508','5','math','张艺馨','138********',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','徐州');declared翻译中文
INSERT INTO city(id,NAME) VALUES('zj','镇江');
INSERT INTO city(id,NAME) VALUES('yz','扬州');
问题描述：查询学⽣在各个城市⼈数分布情况，并在最后⼀⾏数据显⽰⼈数总计结果。在使⽤with rollup语句时最后⼀⾏的字段显⽰为null，显得特别别扭。
查询语句：
SELECT ci.name AS '城市',
COUNT(ci.name) AS '⼈数'
FROM student AS st
JOIN city AS cimysql语句分类
ON st.`cityid`=ci.`id`
GROUP BY ci.name WITH ROLLUP;
结果：
安卓在线解析json现在要求将这个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;
结果：
模板⼤功告成？
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论