SQL面试题（十六）

问题:

    现有表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;