group by子句使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总数,即统计记录数量。
创建测试表:
create table tb_test
(
id int auto_increment primary key,
name varchar(20) null,
age decimal(11) null,
year int(4) null,
score int(3)
);
插入数据:
insert into tb_test(name, age, year, score) values ('cj1', 3, 2016, 85), ('cj2', 4, 2015, 90), ('cj3', 5, 2014, 95);
- 执行查询:
select * from tb_test;
- 执行查询:
select name, SUM(score) FROM tb_test GROUP BY name;
- 执行查询:
select name, SUM(score) FROM tb_test GROUP BY name WITH ROLLUP;
- 可使用mysql函数处理查询结果展示:
SELECT ifnull(name, 'total') AS 'total',SUM(score) FROM tb_test GROUP BY name WITH ROLLUP;
SELECT coalesce(name, 'total') AS 'total', SUM(score) FROM tb_test GROUP BY name WITH ROLLUP;
结果: