Has three tables, member is a member list, the order is the order list, recharge is prepaid phone table







Now you need to calculate each member of the total order and top-up amount, the query results as shown in the figure below:



How to write SQL, please?

SQL structure and data is as follows:

 
The SET NAMES utf8mb4; 
The SET FOREIGN_KEY_CHECKS=0; 

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
- Table structure for member 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
DROP TABLE IF the EXISTS ` member `; 
The CREATE TABLE ` member ` (
` id ` int (0) NOT NULL AUTO_INCREMENT, 
` name ` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'name', 
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, 'zhang'); 
INSERT INTO ` member ` VALUES (2, 'bill'); 
INSERT INTO ` member ` VALUES (3, 'Cathy'); 

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
- Table structure for order 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
DROP TABLE IF the EXISTS ` order `; 
The CREATE TABLE ` order ` (
` id ` int (0) NOT NULL AUTO_INCREMENT, 
` orders ` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'orders', 
` memberid ` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'member id, 
PRIMARY KEY (` id `) USING BTREE 
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci COMMENT='order table 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 the EXISTS ` recharge `; 
The CREATE TABLE ` recharge ` (
` id ` int (0) NOT NULL AUTO_INCREMENT, 
` recharges ` int (0) NULL DEFAULT NULL COMMENT 'top-up amount, 
` memberid ` int (0) NULL DEFAULT NULL COMMENT 'member id, 
PRIMARY KEY (` id `) USING BTREE 
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci COMMENT='top-up table 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); 

The SET FOREIGN_KEY_CHECKS=1; 

CodePudding user response:

 
The select m.n ame, sum (o.o rders) as the orders, the sum (r.r echarges) as recharges the from member m 
Left the join ` order on m.i ` o d=o.m emberId 
Left the join recharge r on m.i d=r.m emberId 
Group by m.n ame 

Give a suggestion, database design as far as possible need not, keyword to the program error probability,

CodePudding user response:

Select the ls. *, sum (c.r echarges) from (
The select Anderson d, a.n ame, sum (b.o rders) as the orders from member a
Inner join ` order ` b on Anderson, d=b.m emberid
Group by Anderson, d, a.n ame) ls
Inner join recharge c on the ls. Id=c. emberid
Group by ls. Id, ls. Name
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 zhang 6 2
2 li si 5 4
3 fifty and six (6)