在mysql中,可以借助with rollup得到每个分组的汇总级别的数据。
下面开始今天的测试。
创建测试用表,如下:
复制代码 代码示例:
CREATE TABLE `test3` (
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`name1` varchar(10) DEFAULT NULL,
`name2` varchar(10) DEFAULT NULL,
`cnt` int(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
测试数据为:
1 rank1 subrank1 1
2 rank1 subrank1 2
3 rank2 subrank1 1
4 rank2 subrank2 2
5 rank3 subrank1 1
6 rank1 subrank2 3
查询1:
复制代码 代码示例:
select name1,name2,sum(cnt) from test3 group by name1,name2
输出结果:
rank1 subrank1 3
rank1 subrank2 3
rank2 subrank1 1
rank2 subrank2 2
rank3 subrank1 1
查询2:
复制代码 代码示例:
select name1,name2,sum(cnt) from test3 group by name1,name2 with rollup
输出结果:
rank1 subrank1 3
rank1 subrank2 3
rank1 NULL 6
rank2 subrank1 1
rank2 subrank2 2
rank2 NULL 3
rank3 subrank1 1
rank3 NULL 1
NULL NULL 10由以上结果,可以看到多出了汇总信息,with rollup的妙用,呵呵。