- 具有多个事务的mysql查询

  显示原文与译文双语对照的内容
58 4

我有这个示例表的结构和记录:

-- ----------------------------
-- Table structure for driver
-- ----------------------------
DROP TABLE IF EXISTS `driver`;
CREATE TABLE `driver` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `fullname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Table structure for taxi
-- ----------------------------
DROP TABLE IF EXISTS `taxi`;
CREATE TABLE `taxi` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `unit` varchar(5) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for debts
-- ----------------------------
DROP TABLE IF EXISTS `debts`;
CREATE TABLE `debts` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `data` float(10,2) NOT NULL DEFAULT '0.00',
 `driver` bigint(20) NOT NULL,
 `dateadded` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for dispatch
-- ----------------------------
DROP TABLE IF EXISTS `dispatch`;
CREATE TABLE `dispatch` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `driver` int(11) NOT NULL,
 `taxi` int(11) NOT NULL,
 `dispatchdate` date DEFAULT NULL,
 `rate` float DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1790 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for rpayment
-- ----------------------------
DROP TABLE IF EXISTS `rpayment`;
CREATE TABLE `rpayment` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dateadded` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for rpayment_detail
-- ----------------------------
DROP TABLE IF EXISTS `rpayment_detail`;
CREATE TABLE `rpayment_detail` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `rpayment` bigint(20) NOT NULL,
 `dispatch` bigint(20) NOT NULL,
 `amount` float DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `driver` VALUES ('1', 'DRIVER1');
INSERT INTO `driver` VALUES ('2', 'DRIVER2');
INSERT INTO `driver` VALUES ('3', 'DRIVER3');
INSERT INTO `taxi` VALUES ('1', 'UNIT1');
INSERT INTO `taxi` VALUES ('2', 'UNIT2');
INSERT INTO `taxi` VALUES ('3', 'UNIT3');
INSERT INTO `debts` VALUES ('1','100.00', '1', '2012-04-01 16:07:15');
INSERT INTO `debts` VALUES ('2','200.00', '1', '2012-04-01 16:25:56');
INSERT INTO `debts` VALUES ('3','300.00', '3', '2012-04-01 16:34:42');
INSERT INTO `debts` VALUES ('4','400.00', '2', '2012-04-02 00:11:10');
INSERT INTO `debts` VALUES ('5','200.00', '1', '2012-04-02 00:57:58');
INSERT INTO `debts` VALUES ('6','500.00', '3', '2012-04-02 10:25:39');
INSERT INTO `debts` VALUES ('7','100.00', '2', '2012-04-02 11:15:25');
INSERT INTO `dispatch` VALUES ('1', '1', '1', '2012-04-01', '1000');
INSERT INTO `dispatch` VALUES ('2', '2', '2', '2012-04-01', '1000');
INSERT INTO `dispatch` VALUES ('3', '3', '3', '2012-04-01', '1000');
INSERT INTO `dispatch` VALUES ('4', '1', '1', '2012-04-02', '1000');
INSERT INTO `dispatch` VALUES ('5', '2', '2', '2012-04-02', '1000');
INSERT INTO `dispatch` VALUES ('6', '3', '3', '2012-04-02', '1000');
INSERT INTO `rpayment` VALUES ('1', '2012-04-30 20:11:16');
INSERT INTO `rpayment` VALUES ('2', '2012-05-03 03:25:31');
INSERT INTO `rpayment_detail` VALUES ('1', '1', '1', '1000');
INSERT INTO `rpayment_detail` VALUES ('2', '1', '4', '0');
INSERT INTO `rpayment_detail` VALUES ('3', '2', '2', '0');
INSERT INTO `rpayment_detail` VALUES ('4', '2', '5', '500');

我想查看如下结果:

UNIT DRIVER RPAYMENT_TOTAL TOTAL_DEBTS
--------------------------------------------------
UNIT1 DRIVER1 1000 500
UNIT2 DRIVER2 500 500
UNIT3 DRIVER3 0 800

我现在有这个。

SELECT taxi.unit, driver.fullname, SUM(rpayment_detail.amount) AS rpayment_total, 
 SUM(debts.`data`) AS total_debts 
FROM driver 
LEFT JOIN debts ON (driver.id = debts.driver) 
LEFT JOIN dispatch ON (driver.id = dispatch.driver) 
LEFT JOIN rpayment_detail ON (dispatch.id = rpayment_detail.dispatch) 
LEFT JOIN rpayment ON (rpayment_detail.rpayment = rpayment.id) 
LEFT JOIN taxi ON (dispatch.taxi = taxi.id) 
GROUP BY driver.id 
ORDER BY taxi.unit asc, driver.fullname asc

结果是。

UNIT DRIVER RPAYMENT_TOTAL TOTAL_DEBTS
--------------------------------------------------
UNIT1 DRIVER1 3000 1000.00
UNIT2 DRIVER2 1000 1000.00
UNIT3 DRIVER3 null 1600.00
时间:原作者:0个回答

71 1

不能用你拥有的表结构来完成。

问题在于,debts 需要一个外键列来替代。

如果没有这种情况,每个债务将被加入每个调度,有效地乘以司机的数量。

原作者:
...