今天工作遇到个需求:列表展示用户信息,同时要展示每个用户下的订单数量orderCount,总的消费金额consumeMoney和绑定的车辆数carCount。共三张表:t_app_user(主表),t_app_order,t_app_car。思路是把查询的结果封装到appUserEntity实体类的VO层中统一返回给前端。然后开始写sql,如下页面图:
上面截图是旧的,下面贴出的表是我阉割后的,图片中的有些字段user表中会没有,主要看红色字体标记的字段。上表数据,用户信息表 t_app_user:
CREATE TABLE `t_app_user` (
`id` bigint(20) NOT NULL COMMENT '算法实现,主键',
`account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '账号',
`password` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
`gender` smallint(1) NULL DEFAULT NULL COMMENT '性别:1-男, 2-女, 3-未知的性别',
`mobile` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '联系电话',
`balance` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '余额',
`picture` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像',
`number` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '客户编号',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '详细地址',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
`flag` smallint(1) NOT NULL DEFAULT 1 COMMENT '删除标识: 1-正常, 0-删除',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'app用户表' ROW_FORMAT = Compact;
-- ----------------------------
-- Records of t_app_user
-- ----------------------------
INSERT INTO `t_app_user` VALUES (1001, 'admin', '123', '测试数据', 1, '13564758921', 100.00, NULL, NULL, NULL, NULL, 1, '2021-11-29 10:10:15', '2021-11-30 10:10:19');
INSERT INTO `t_app_user` VALUES (1002, 'test1', 'test1', '后台测试', 1, '1352525255', 200.00, NULL, NULL, NULL, NULL, 1, '2021-12-01 14:27:20', '2021-12-01 14:27:20');
SET FOREIGN_KEY_CHECKS = 1;
订单表 t_app_order:
CREATE TABLE `t_app_order` (
`id` bigint(20) NOT NULL COMMENT '算法实现,主键',
`app_user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户id',
`lock_id` bigint(20) NULL DEFAULT NULL COMMENT '地锁id',
`consume_money` decimal(10, 0) NULL DEFAULT NULL COMMENT '消费金额',
`pay_type` smallint(1) NULL DEFAULT NULL COMMENT '消费类型:1-充电费, 2-占位费',
`order_number` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单编号',
`flag` smallint(1) NOT NULL DEFAULT 1 COMMENT '删除标识: 1-正常, 0-删除',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表' ROW_FORMAT = Compact;
-- ----------------------------
-- Records of t_app_order
-- ----------------------------
INSERT INTO `t_app_order` VALUES (1, 1001, NULL, 500, NULL, NULL, 1, '2021-12-01 11:21:53', '2021-12-01 11:21:53');
INSERT INTO `t_app_order` VALUES (2, 1001, NULL, 300, NULL, NULL, 1, '2021-12-01 11:21:53', '2021-12-01 11:21:53');
INSERT INTO `t_app_order` VALUES (3, 1002, NULL, 200, NULL, NULL, 1, '2021-12-01 11:30:49', '2021-12-01 11:30:49');
SET FOREIGN_KEY_CHECKS = 1;
车辆表 t_app_car:
CREATE TABLE `t_app_car` (
`id` bigint(20) NOT NULL COMMENT '算法实现,主键',
`user_id` bigint(20) NULL DEFAULT NULL COMMENT '客户id,关联t_app_user',
`car_number` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车牌号',
`bind_status` smallint(1) NOT NULL DEFAULT 1 COMMENT '绑定状态: 1-已绑定, 2-已解除',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
`flag` smallint(1) NOT NULL DEFAULT 1 COMMENT '删除标识: 1-正常, 0-删除',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '车辆信息表' ROW_FORMAT = Compact;
-- ----------------------------
-- Records of t_app_car
-- ----------------------------
INSERT INTO `t_app_car` VALUES (10001, 1001, '京M3654G9', 1, NULL, 1, '2021-11-28 09:20:40', '2021-11-30 09:20:43');
INSERT INTO `t_app_car` VALUES (10002, 1001, '京A3648G7', 1, NULL, 1, '2021-11-29 15:04:24', '2021-11-30 15:04:28');
INSERT INTO `t_app_car` VALUES (10003, 1002, '沪A4568H9', 1, NULL, 1, '2021-11-29 17:05:06', '2021-11-30 17:05:12');
INSERT INTO `t_app_car` VALUES (10004, 1001, '京M3654F4', 1, NULL, 1, '2021-11-28 09:20:40', '2021-11-30 09:20:43');
INSERT INTO `t_app_car` VALUES (10005, 1002, '京1111111', 1, NULL, 1, '2021-12-01 14:27:48', '2021-12-01 14:27:48');
INSERT INTO `t_app_car` VALUES (915537685781676032, 1001, '湘A115G64', 0, NULL, 1, '2021-12-01 09:39:54', '2021-12-01 09:39:54');
INSERT INTO `t_app_car` VALUES (915538594502803456, 1001, '京M3654G9', 1, NULL, 1, '2021-12-01 09:43:31', '2021-12-01 09:43:31');
INSERT INTO `t_app_car` VALUES (915553651265634304, 1001, '京M3654G8', 2, NULL, 1, '2021-12-01 10:43:21', '2021-12-01 10:43:21');
INSERT INTO `t_app_car` VALUES (915560555006132224, 1001, '京M3654G9', 1, NULL, 1, '2021-12-01 11:10:47', '2021-12-01 11:10:47');
SET FOREIGN_KEY_CHECKS = 1;
sql如下:
select a.id,a.name,b.orderCount,b.consumeMoney,c.carCount
from t_app_user a
left JOIN (select tao.user_id as userId,COUNT(0) as orderCount,SUM(consume_money) as consumeMoney from t_app_order tao GROUP BY userId) as b on b.userId = a.id
left JOIN (select tac.user_id as userId,COUNT(0) as carCount from t_app_car tac GROUP BY userId) as c on c.userId = a.id
执行结果:
大致思想就是,先分别把两张从表中需要的字段先查出来,然后把查出来的这几个字段当做一个从表去联主表;换言之先做从表的子查询,然后把子查询的结果当做一张表去联主表。当然要注意在子查询中就要Group By,而不是在整个sql的最后面进行Group By,否则查出的数据的值会出错。
我是新手,花了一些时间才搞出来,希望能帮到有需要的人。当然如果有需要改进的地方请各位大佬批评指出。