如何实现复杂的多表查询【简要版】

简要版】"/>

如何实现复杂的多表查询【简要版】

最近，我独自在负责一个关于二级分销方面的大模块，其中一小块的设计中要求查询如下信息：

涉及到的数据表结构如下：

1.推广员表

CREATE TABLE `dis_populist` (`PromotersId` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`ShopId` int(11) DEFAULT NULL COMMENT '商铺id',`UserId` int(11) DEFAULT NULL COMMENT '用户id',`ParentId` int(11) DEFAULT NULL COMMENT '父id',`PopulistLevelId` int(2) DEFAULT NULL COMMENT '推广员级别',`InvitationCode` varchar(6) DEFAULT NULL COMMENT '邀请码（随机生成6位数字大小写字母）',`AddTime` datetime DEFAULT NULL COMMENT '添加时间',`UpdateTime` datetime DEFAULT NULL COMMENT '修改时间',PRIMARY KEY (`PromotersId`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='推广员表';

2.用户表

CREATE TABLE `edu_user` (`USER_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '学员ID',`stuNum` varchar(20) DEFAULT NULL COMMENT '校园学号',`MOBILE` varchar(11) DEFAULT NULL COMMENT '手机号',`EMAIL` varchar(50) DEFAULT NULL COMMENT '邮箱号',`PASSWORD` varchar(64) DEFAULT NULL COMMENT '密码',`USER_NAME` varchar(50) DEFAULT NULL COMMENT '用户名',`SHOW_NAME` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '显示名 （昵称）',`SEX` tinyint(4) DEFAULT '0' COMMENT '性别  1男  2女',`AGE` tinyint(3) DEFAULT '0' COMMENT '年龄',`CREATE_TIME` timestamp NULL DEFAULT NULL COMMENT '注册时间',`IS_AVALIBLE` tinyint(4) DEFAULT '1' COMMENT '是否可用 1正常  2冻结',`PIC_IMG` varchar(255) DEFAULT NULL COMMENT '用户头像',`BANNER_URL` varchar(255) DEFAULT NULL COMMENT '个人中心用户背景图片',`MSG_NUM` int(11) DEFAULT '0' COMMENT '站内信未读消息数',`SYS_MSG_NUM` int(11) DEFAULT '0',`LAST_SYSTEM_TIME` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '上传统计系统消息时间',`REGISTER_FROM` varchar(20) DEFAULT 'register' COMMENT '注册来源',`LOGIN_ACCOUNT` varchar(255) DEFAULT NULL COMMENT '登录账号',`company` varchar(128) DEFAULT NULL,`REFEREE` varchar(255) DEFAULT NULL COMMENT '推荐人字段',`ROLE_ID` varchar(64) DEFAULT '0' COMMENT '角色（0：学生，1：老师，2：代理，3：审核者,4：负责人）',`USER_EMAIL_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '帐号邮箱状态（1未验证,非1已验证）',`auxiliary_teacher_id` int(11) DEFAULT '0' COMMENT '辅助老师的id',`department` varchar(128) DEFAULT ' ' COMMENT '部门/单位（企业用）',`college` varchar(32) DEFAULT NULL COMMENT '学院（高校用）',`major` varchar(32) DEFAULT NULL COMMENT '专业（高校用）',`classes` varchar(32) DEFAULT NULL COMMENT '班级（高校用）',`VIP_STATUS` int(1) DEFAULT NULL,PRIMARY KEY (`USER_ID`),KEY `user_id_index` (`USER_ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=55465 DEFAULT CHARSET=utf8 COMMENT='用户表';

3.推广员等级表

CREATE TABLE `dis_populistlevel` (`PopulistLevelId` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`PopulistLevel` int(11) DEFAULT NULL COMMENT '推广员级别（1：青铜；2：白银；3：黄金）',`LevelName` varchar(8) DEFAULT NULL COMMENT '级别名称',`AddTime` datetime DEFAULT NULL COMMENT '添加时间',`UpdateTime` datetime DEFAULT NULL COMMENT '修改时间',PRIMARY KEY (`PopulistLevelId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='推广员等级表';

4.课程订单表

CREATE TABLE `edu_orders` (`ORDER_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',`USER_ID` int(11) DEFAULT '0' COMMENT '用户ID',`ORDER_NO` varchar(50) DEFAULT NULL COMMENT '订单号',`SUM_MONEY` decimal(10,2) DEFAULT '0.00' COMMENT '订单总金额',`STATES` varchar(10) DEFAULT NULL COMMENT '订单状态 SUCCESS已支付 INIT未支付  CANCEL已取消',`CREATE_TIME` timestamp NULL DEFAULT NULL COMMENT '订单创建时间',`PAY_TIME` timestamp NULL DEFAULT NULL COMMENT '订单支付时间',`SYS_USER_ID` int(11) DEFAULT '0' COMMENT '审核用户ID',`PAY_TYPE` varchar(50) DEFAULT 'ALIPAY' COMMENT '支付类型',`req_channel` varchar(20) DEFAULT NULL COMMENT '请求渠道(WEB,APP)',`description` varchar(500) DEFAULT NULL COMMENT '备用描述',`version` int(11) DEFAULT NULL COMMENT '乐观锁版本号',`req_ip` varchar(15) DEFAULT NULL COMMENT '客户端IP',`order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单原始金额',`coupon_amount` decimal(10,2) DEFAULT NULL COMMENT '优惠券金额',`couponCode_id` int(11) DEFAULT NULL COMMENT '优惠券编码id',`refund_amount` decimal(10,2) DEFAULT NULL COMMENT '退款金额',`out_trade_no` varchar(200) DEFAULT NULL COMMENT '第三方支付商户订单号',`DirectBenefitPersonId` int(11) DEFAULT NULL COMMENT '直接推广员id',`IndirectBenefitPersonId` int(11) DEFAULT NULL COMMENT '间接推广员id',PRIMARY KEY (`ORDER_ID`),KEY `order_no` (`ORDER_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=86263 DEFAULT CHARSET=utf8 COMMENT='课程订单表';

5.流水表

CREATE TABLE `edu_trxorder_detail` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`user_id` int(11) NOT NULL COMMENT '用户id',`course_id` int(11) unsigned NOT NULL COMMENT '相关联的课程id/套餐id（前台快照）',`trxorder_id` int(11) NOT NULL DEFAULT '0' COMMENT '交易订单ID',`membertype` tinyint(3) DEFAULT '0' COMMENT '会员观看类型（前台快照）',`losetype` int(3) NOT NULL DEFAULT '0' COMMENT '有效期类型（前台快照）',`lose_abs_time` datetime DEFAULT NULL COMMENT '订单过期时间段（前台快照）',`lose_time` varchar(255) DEFAULT NULL COMMENT '订单过期时间点（前台快照）',`auth_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '课程过期时间',`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '下单时间',`pay_time` datetime DEFAULT NULL COMMENT '支付成功时间',`source_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '原价格（前台快照）',`coupon_price` decimal(10,2) DEFAULT '0.00' COMMENT '优惠价格',`current_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '销售价格（前台快照）',`course_name` varchar(255) NOT NULL DEFAULT '' COMMENT '课程名称（前台goods快照）',`trx_status` char(15) NOT NULL DEFAULT '' COMMENT '订单状态（前台goods快照）',`auth_status` char(10) NOT NULL DEFAULT '' COMMENT '课程状态（INIT，SUCCESS，REFUND，CLOSED，LOSED）',`request_id` varchar(50) NOT NULL DEFAULT '' COMMENT '订单请求号',`description` varchar(50) NOT NULL DEFAULT '' COMMENT '描述',`version` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '乐观锁版本号',`last_update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最后更新时间',`remind_status` varchar(255) DEFAULT 'INIT' COMMENT '过期是否提醒 INIT 未提醒 ALREADY 已提醒',`mustLook` int(1) DEFAULT '0' COMMENT '1是防刷,进度条不可拖动,0是可拖动',`DirectBenefit` decimal(10,2) DEFAULT NULL COMMENT '直接推广员获得的佣金（元）',`IndirectBenefit` decimal(10,2) DEFAULT NULL COMMENT '间接推广员获得的佣金（元）',PRIMARY KEY (`id`),KEY `user_id` (`user_id`),KEY `trxorder_id` (`trxorder_id`),KEY `IDX_REQUEST_ID` (`request_id`),KEY `course_id_index` (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=88877 DEFAULT CHARSET=utf8 COMMENT='流水表';

由上可知，【头像，昵称，推广员等级名称，姓名，是否是下级，加入时间】这6个属性很容易查询出来，而【累计邀请，累计收益】这2个属性需要进行多表统计查询，怎么办呢？然后，我思考了一会儿，而后写下了以下SQL：

SELECTe.userId '用户ID',e.picImg '头像',e.showName '昵称',e.levelName '推广员等级',e.userName '姓名',e.parentId '是否是下级',e.cumulativeInviter '累计邀请人',e.totalmoney '累计收益',e.addTime '加入时间'
FROMedu_orders o,(SELECTw.userId userId,w.addTime addTime,w.parentId parentId,w.showName showName,w.userName userName,w.picImg picImg,w.levelName levelName,(w.totalDirectBenefit + r.totalIndirectBenefit) totalmoney,count(w.UserId) cumulativeInviterFROMdis_populist d,(SELECTq.userId userId,q.addTime addTime,q.parentId parentId,q.showName showName,q.userName userName,q.picImg picImg,q.levelName levelName,SUM(DirectBenefit) totalDirectBenefitFROMedu_trxorder_detail,(SELECTt.userId userId,t.addTime addTime,t.parentId parentId,t.showName showName,t.userName userName,t.picImg picImg,t.levelName levelNameFROMedu_trxorder_detail,(SELECTp.UserId userId,p.AddTime addTime,p.ParentId parentId,u.SHOW_NAME showName,u.USER_NAME userName,u.PIC_IMG picImg,l.LevelName levelNameFROMdis_populist pLEFT JOIN edu_user u ON p.UserId = u.USER_IDLEFT JOIN dis_populistlevel l ON p.PopulistLevelId = l.PopulistLevelIdWHEREp.ParentId = (SELECTPromotersIdFROMdis_populistWHEREUserId = 23288)) tGROUP BYt.userIdORDER BYt.AddTime) qWHERErequest_id IN (SELECTORDER_NOFROMedu_ordersWHERE(PAY_TYPE = 'ALIPAY'OR PAY_TYPE = 'WEIXIN')AND DirectBenefitPersonId IN (q.userId))GROUP BYq.userIdORDER BYq.AddTime) w,(SELECTq.userId userId,q.addTime addTime,q.parentId parentId,q.showName showName,q.userName userName,q.picImg picImg,q.levelName levelName,SUM(IndirectBenefit) totalIndirectBenefitFROMedu_trxorder_detail,(SELECTt.userId userId,t.addTime addTime,t.parentId parentId,t.showName showName,t.userName userName,t.picImg picImg,t.levelName levelNameFROMedu_trxorder_detail,(SELECTp.UserId userId,p.AddTime addTime,p.ParentId parentId,u.SHOW_NAME showName,u.USER_NAME userName,u.PIC_IMG picImg,l.LevelName levelNameFROMdis_populist pLEFT JOIN edu_user u ON p.UserId = u.USER_IDLEFT JOIN dis_populistlevel l ON p.PopulistLevelId = l.PopulistLevelIdWHEREp.ParentId = (SELECTPromotersIdFROMdis_populistWHEREUserId = 23288)) tGROUP BYt.userIdORDER BYt.AddTime) qWHERErequest_id IN (SELECTORDER_NOFROMedu_ordersWHERE(PAY_TYPE = 'ALIPAY'OR PAY_TYPE = 'WEIXIN')AND IndirectBenefitPersonId IN (q.userId)GROUP BYq.userIdORDER BYq.AddTime)) rWHEREd.ParentId IN (SELECTPromotersIdFROMdis_populistWHEREUserId IN (w.userId))GROUP BYw.userIdORDER BYw.AddTime) e
WHEREo.USER_ID IN (e.userId)
GROUP BYe.userId
ORDER BYe.AddTime;

得到的查询结果如下图所示：

OK，大功告成，但是仍有缺陷，我们下期再见！