/*
Navicat MySQL Data TransferSource Server : localhost_3306
Source Server Version : 50067
Source Host : localhost:3306
Source Database : testTarget Server Type : MYSQL
Target Server Version : 50067
File Encoding : 65001Date: 2010-12-03 19:17:47
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `test`
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`level` varchar(50) default NULL,
`dangwei` varchar(50) default NULL,
`value` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gb2312;-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', 'A', 'ONE', '件', '5');
INSERT INTO `test` VALUES ('2', 'B', 'TWO', '支', '10');
INSERT INTO `test` VALUES ('3', 'A', 'TWO', '件', '3');
INSERT INTO `test` VALUES ('4', 'A', 'ONE', '支', '15');
INSERT INTO `test` VALUES ('5', 'B', 'ONE', '件', '2');
把name,level相同的记录进行合计,当dangwei是件时需要乘以17(1件等于17支)
Navicat MySQL Data TransferSource Server : localhost_3306
Source Server Version : 50067
Source Host : localhost:3306
Source Database : testTarget Server Type : MYSQL
Target Server Version : 50067
File Encoding : 65001Date: 2010-12-03 19:17:47
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `test`
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`level` varchar(50) default NULL,
`dangwei` varchar(50) default NULL,
`value` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gb2312;-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', 'A', 'ONE', '件', '5');
INSERT INTO `test` VALUES ('2', 'B', 'TWO', '支', '10');
INSERT INTO `test` VALUES ('3', 'A', 'TWO', '件', '3');
INSERT INTO `test` VALUES ('4', 'A', 'ONE', '支', '15');
INSERT INTO `test` VALUES ('5', 'B', 'ONE', '件', '2');
把name,level相同的记录进行合计,当dangwei是件时需要乘以17(1件等于17支)
+----+------+-------+---------+-------+
| id | name | level | dangwei | value |
+----+------+-------+---------+-------+
| 1 | A | ONE | 件 | 5 |
| 2 | B | TWO | 支 | 10 |
| 3 | A | TWO | 件 | 3 |
| 4 | A | ONE | 支 | 15 |
| 5 | B | ONE | 件 | 2 |
+----+------+-------+---------+-------+
5 rows in set (0.00 sec)mysql> select name,level,SUM(value*if(dangwei='件',17,1)) as `合计`
-> from test
-> Group by name,level;
+------+-------+------+
| name | level | 合计 |
+------+-------+------+
| A | ONE | 100 |
| A | TWO | 51 |
| B | ONE | 34 |
| B | TWO | 10 |
+------+-------+------+
4 rows in set (0.09 sec)mysql>
SELECT sum(if(dangwei='件',17,1)*value)
FROM test group by name,level
17支
else
1支
end if