数据库版本:MYSQL 5.1.41
表结构
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`(
`id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2`(
`tid` INT(1) UNSIGNED NOT NULL,
`qty` INT(1) UNSIGNED NOT NULL DEFAULT '0',
`in_wgt` DOUBLE(8,2) UNSIGNED NOT NULL DEFAULT '0.00',
`out_wgt` DOUBLE(8,2) UNSIGNED NOT NULL DEFAULT '0.00',
KEY `tid`(`tid`),
CONSTRAINT `t_t2_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
qty字段的数据是固定的,所以没必要弄一个in_qty和out_qty,但又想让它在视图中显示出"in_qty"和"out_qty"
该如何做呢??测试数据
INSERT INTO `t1`(`name`) VALUES('A'),('B'),('C'),('D');
INSERT INTO `t2` VALUES(1,12,3.21,0),(1,12,0,0),(1,12,3.45,3.42),(1,12,3.41,3.12),(2,12,0,0),(2,12,0,0),(2,9,5.65,0),(3,12,6.12,5.41),(3,12,0,0),(4,12,0,0),(4,12,0,0),(4,8,0,0);期望结果
+-----+------+------+------+------+
| tid | iqty | oqty | iwgt | owgt |
+-----+------+------+------+------+
| __1 | __36 | __24 | 10.07| 6.56 |
| __2 | ___9 | ___0 | 5.65 | ___0 |
| __3 | __12 | __12 | 6.12 | 5.41 |
| __4 | ___0 | ___0 | ___0 | ___0 |
+-----+------+------+------+------+
表结构
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`(
`id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2`(
`tid` INT(1) UNSIGNED NOT NULL,
`qty` INT(1) UNSIGNED NOT NULL DEFAULT '0',
`in_wgt` DOUBLE(8,2) UNSIGNED NOT NULL DEFAULT '0.00',
`out_wgt` DOUBLE(8,2) UNSIGNED NOT NULL DEFAULT '0.00',
KEY `tid`(`tid`),
CONSTRAINT `t_t2_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
qty字段的数据是固定的,所以没必要弄一个in_qty和out_qty,但又想让它在视图中显示出"in_qty"和"out_qty"
该如何做呢??测试数据
INSERT INTO `t1`(`name`) VALUES('A'),('B'),('C'),('D');
INSERT INTO `t2` VALUES(1,12,3.21,0),(1,12,0,0),(1,12,3.45,3.42),(1,12,3.41,3.12),(2,12,0,0),(2,12,0,0),(2,9,5.65,0),(3,12,6.12,5.41),(3,12,0,0),(4,12,0,0),(4,12,0,0),(4,8,0,0);期望结果
+-----+------+------+------+------+
| tid | iqty | oqty | iwgt | owgt |
+-----+------+------+------+------+
| __1 | __36 | __24 | 10.07| 6.56 |
| __2 | ___9 | ___0 | 5.65 | ___0 |
| __3 | __12 | __12 | 6.12 | 5.41 |
| __4 | ___0 | ___0 | ___0 | ___0 |
+-----+------+------+------+------+
-> sum(if(in_wgt>0,qty,0)) as iqty,
-> sum(if(out_wgt>0,qty,0)) as oqty,
-> sum(in_wgt) as iwgt ,
-> sum(out_wgt) as owgt
-> from t2
-> group by tid;
+-----+------+------+-------+------+
| tid | iqty | oqty | iwgt | owgt |
+-----+------+------+-------+------+
| 1 | 36 | 24 | 10.07 | 6.54 |
| 2 | 9 | 0 | 5.65 | 0.00 |
| 3 | 12 | 12 | 6.12 | 5.41 |
| 4 | 0 | 0 | 0.00 | 0.00 |
+-----+------+------+-------+------+
4 rows in set (0.05 sec)mysql>
刚开始时也是用IF的...但把SUM写到IF里面了...Orz
感谢版主大大!!