mysql 脚本:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb`
-- ----------------------------
DROP TABLE IF EXISTS `tb`;
CREATE TABLE `tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`price` varchar(20) DEFAULT NULL,
`date1` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb
-- ----------------------------
INSERT INTO `tb` VALUES ('1', '烟机', '300', '2010-08-20');
INSERT INTO `tb` VALUES ('2', '灶具', '100', '2010-08-19');
INSERT INTO `tb` VALUES ('3', '烟机', '350', '2010-08-19');
INSERT INTO `tb` VALUES ('4', '灶具', '150', '2010-07-20');
INSERT INTO `tb` VALUES ('5', '灶具', '200', '2010-07-21');
INSERT INTO `tb` VALUES ('6', '烟机', '315', '2010-07-30');
如何用mysql 语句实现如下 分组统计:
按date1(月份)和name分组,分别统计price的总价钱结果如下:
date1 name total
2010-07 灶具 350
2010-07 烟机 315
2010-08 烟机 650
2010-08 灶具 100
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb`
-- ----------------------------
DROP TABLE IF EXISTS `tb`;
CREATE TABLE `tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`price` varchar(20) DEFAULT NULL,
`date1` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb
-- ----------------------------
INSERT INTO `tb` VALUES ('1', '烟机', '300', '2010-08-20');
INSERT INTO `tb` VALUES ('2', '灶具', '100', '2010-08-19');
INSERT INTO `tb` VALUES ('3', '烟机', '350', '2010-08-19');
INSERT INTO `tb` VALUES ('4', '灶具', '150', '2010-07-20');
INSERT INTO `tb` VALUES ('5', '灶具', '200', '2010-07-21');
INSERT INTO `tb` VALUES ('6', '烟机', '315', '2010-07-30');
如何用mysql 语句实现如下 分组统计:
按date1(月份)和name分组,分别统计price的总价钱结果如下:
date1 name total
2010-07 灶具 350
2010-07 烟机 315
2010-08 烟机 650
2010-08 灶具 100
from tb
group by date_format(date1,'%Y-%m'),name
from tb
group by left(date1,7),name
from tb
group by date_format(date1,'%Y-%m'),name