列表查询中多表关联字段展示的SQL写法

今天工作遇到个需求：列表展示用户信息，同时要展示每个用户下的订单数量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，否则查出的数据的值会出错。

我是新手，花了一些时间才搞出来，希望能帮到有需要的人。当然如果有需要改进的地方请各位大佬批评指出。