有三張表，member是成員表、order是訂單表、recharge是充值表
現在需要計算每個成員的訂單總額和充值總額，查詢結果如下圖所示：
請問sql如何寫？
sql結構和資料如下：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for member
-- ----------------------------
DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of member
-- ----------------------------
INSERT INTO `member` VALUES (1, '張三');
INSERT INTO `member` VALUES (2, '李四');
INSERT INTO `member` VALUES (3, '王五');
-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`orders` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '訂單金額',
`memberid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '成員id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '訂單表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, '3', '1');
INSERT INTO `order` VALUES (2, '3', '1');
INSERT INTO `order` VALUES (3, '5', '2');
INSERT INTO `order` VALUES (4, '6', '3');
-- ----------------------------
-- Table structure for recharge
-- ----------------------------
DROP TABLE IF EXISTS `recharge`;
CREATE TABLE `recharge` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`recharges` int(0) NULL DEFAULT NULL COMMENT '充值金額',
`memberid` int(0) NULL DEFAULT NULL COMMENT '成員id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '充值表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of recharge
-- ----------------------------
INSERT INTO `recharge` VALUES (1, 2, 1);
INSERT INTO `recharge` VALUES (2, 3, 2);
INSERT INTO `recharge` VALUES (3, 1, 2);
INSERT INTO `recharge` VALUES (4, 6, 3);
SET FOREIGN_KEY_CHECKS = 1;
uj5u.com熱心網友回復：
select m.name,sum(o.orders) as orders,sum(r.recharges) as recharges from member m
left join `order` o on m.id=o.memberId
left join recharge r on m.id=r.memberId
group by m.name
給個建議，資料庫設計盡量不用關鍵字，給程式增加出錯概率。
uj5u.com熱心網友回復：
select ls.*,sum(c.recharges) from (select a.id,a.name ,sum(b.orders) as orders from member a
inner join `order` b on a.id = b.memberid
group by a.id,a.name) ls
inner join recharge c on ls.id = c.memberid
group by ls.id,ls.name
---------------------
1 張三 6 2
2 李四 5 4
3 王五 6 6
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/173510.html
標籤：MySQL