题目:
现有一家公司年收益表如下(表名:t,为了便于比较结果是否正确,所以把里面的收益变成1):
问题:统计每年之前的历年收益之和?
SELECT a.YEAR, ifnull(( SELECT sum( b.benifit ) FROM t b WHERE a.YEAR > b.YEAR ),
0
) totolBenifit
FROM
( SELECT YEAR FROM t GROUP BY YEAR ) a
结果:
解释:
让表a中的每一年都和表b去比较,只要比这一年小就计算总和,因为表a中一年有4个季度,我先进行了分组,所以select
后面的子查询语句就会执行很少的次数,相比使用distinct去重
效果更好
建表语句:
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`year` char(4) DEFAULT NULL,
`quarter` char(1) DEFAULT NULL,
`benifit` int(20) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
INSERT INTO `t` VALUES ('1', '2014', '一', '1');
INSERT INTO `t` VALUES ('2', '2014', '二', '1');
INSERT INTO `t` VALUES ('3', '2014', '三', '1');
INSERT INTO `t` VALUES ('4', '2014', '四', '1');
INSERT INTO `t` VALUES ('5', '2015', '一', '1');
INSERT INTO `t` VALUES ('6', '2015', '二', '1');
INSERT INTO `t` VALUES ('7', '2015', '三', '1');
INSERT INTO `t` VALUES ('8', '2015', '四', '1');
INSERT INTO `t` VALUES ('9', '2016', '一', '1');
INSERT INTO `t` VALUES ('10', '2016', '二', '1');
INSERT INTO `t` VALUES ('11', '2016', '三', '1');
INSERT INTO `t` VALUES ('12', '2016', '四', '1');
INSERT INTO `t` VALUES ('13', '2017', '一', '1');
INSERT INTO `t` VALUES ('14', '2017', '二', '1');
INSERT INTO `t` VALUES ('15', '2017', '三', '1');
INSERT INTO `t` VALUES ('16', '2017', '四', '1');