问题:
现有表bill和表payment结构如下,两表通过pay_id关联:
bill表――bill_id是帐单标识,charge为帐单金额,pay_id为付款编号,PK:bill_id
bill_id INTEGER
charge INTEGER
pay_id INTEGER
payment表――pay_id为付款编号,charge为付款金额,PK:pay_id
pay_id INTEGER
charge INTEGER
要求更新payment使charge=bill表中同一pay_id的charge之和。
建表:
DROP TABLE IF EXISTS `bill`;
CREATE TABLE `bill` (
`Id` int(11) NOT NULL auto_increment,
`billid` int(11) default NULL,
`charge` int(11) default NULL,
`payid` int(11) default NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `bill` VALUES (1,1,100,2);
INSERT INTO `bill` VALUES (2,2,200,1);
INSERT INTO `bill` VALUES (3,3,223,2);
INSERT INTO `bill` VALUES (4,4,344,3);
INSERT INTO `bill` VALUES (5,5,55,4);
INSERT INTO `bill` VALUES (6,6,66,2);
INSERT INTO `bill` VALUES (7,7,77,1);
INSERT INTO `bill` VALUES (8,8,81,1);
INSERT INTO `bill` VALUES (9,9,88,3);
INSERT INTO `bill` VALUES (10,10,112,3);
DROP TABLE IF EXISTS `payment`;
CREATE TABLE `payment` (
`Id` int(11) NOT NULL auto_increment,
`payid` int(11) default NULL,
`charge` int(11) default NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `payment` VALUES (1,1,358);
INSERT INTO `payment` VALUES (2,2,389);
INSERT INTO `payment` VALUES (3,3,544);
INSERT INTO `payment` VALUES (4,4,55);
INSERT INTO `payment` VALUES (5,5,NULL);
INSERT INTO `payment` VALUES (6,6,NULL);
SQL:
UPDATE payment SET charge =
(SELECT SUM(charge) AS charge FROM bill WHERE bill.payid = payment.payid GROUP BY payid);
推荐一种Order By模式:
SELECT * FROM bill ORDER BY CASE WHEN(Id > 5) THEN charge ELSE payid END;