mysql下的多一表多结果的联合查询外加求和

数据库 waitig 358℃ 百度已收录 0评论

今天在开发过程中,碰到一难题,通过代码可以简单解决,但是考虑到量多时的效率,想通过sql解决下:

题目如何:

有两张表,通过用户id关联,

table1:

CREATE TABLE `tbusiness_evaluation_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `loanrequestid` int(11) DEFAULT NULL,
  `code` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT ‘0.00’,
  `goodstype` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP,
  `desc` varchar(500) CHARACTER SET utf8 DEFAULT NULL,
  `rate` decimal(2,2) DEFAULT ‘0.00’,
  `rateamount` decimal(10,2) DEFAULT ‘0.00’,
  `interestrate` float DEFAULT ‘0’,
  `interestamount` decimal(10,2) DEFAULT NULL,
  `state` int(11) DEFAULT ‘1’,
  `title` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `lockamount` decimal(10,2) DEFAULT ‘0.00’,
  `moneystate` int(11) DEFAULT ‘0’,
  `state1time` datetime DEFAULT NULL,
  `state2time` datetime DEFAULT NULL,
  `state3time` datetime DEFAULT NULL,
  `state1rate` float DEFAULT ‘0’,
  `state2rate` float DEFAULT ‘0’,
  `state3rate` float DEFAULT ‘0’,
  `state1amount` decimal(10,2) DEFAULT ‘0.00’,
  `state2amount` decimal(10,2) DEFAULT ‘0.00’,
  `state3amount` decimal(10,2) DEFAULT ‘0.00’,
  `matchendtime` datetime DEFAULT NULL,
  `state1fund` decimal(10,2) DEFAULT ‘0.00’,
  `state2fund` decimal(10,2) DEFAULT ‘0.00’,
  `state3fund` decimal(10,2) DEFAULT ‘0.00’,
  `inviteid` int(11) DEFAULT ‘0’,
  `platformuseamount1` decimal(10,2) DEFAULT ‘0.00’,
  `platformuseamount2` decimal(10,2) DEFAULT ‘0.00’,
  `state3userid` int(11) DEFAULT ‘0’,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

table2:

DROP TABLE IF EXISTS `tbusiness_pawn_ticket`;
CREATE TABLE `tbusiness_pawn_ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) DEFAULT NULL,
  `state` int(11) DEFAULT ‘1’,
  `starttime` datetime DEFAULT CURRENT_TIMESTAMP,
  `endtime` datetime DEFAULT NULL,
  `pawnid` int(11) DEFAULT ‘0’,
  `reportid` int(11) DEFAULT ‘0’,
  `requestid` int(11) DEFAULT ‘0’,
  `relevantid` int(11) DEFAULT ‘0’,
  `relevanttype` int(1) DEFAULT ‘0’,
  `overdueinterest` decimal(10,2) DEFAULT ‘0.00’,
  `title` varchar(45) DEFAULT NULL,
  `term` int(11) DEFAULT ’30’,
  `userid` int(11) NOT NULL DEFAULT ‘0’,
  `ransomtime` datetime DEFAULT NULL,
  `deadpawntime` datetime DEFAULT NULL,
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP,
  `continuestate` int(11) DEFAULT ‘0’,
  `continuecount` int(11) DEFAULT ‘0’,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

在table1中有三个不同字段来表示用户ID(userid,inviteid,state3userid),且这三个字段的值不会相等,

那就是说通过一个ID获取到userid,nviteid,state3userid等于这个ID的结果值有三个集合,再求和这三个集合不同字段的值的和

方法有三种:

最简单的是方法1:

select b.interestamount,b.state1amount,b.state1rate,
b.state2amount,b.state2rate,b.state3amount,b.state3rate,
b.platformuseamount2,b.userid,b.inviteid,b.state3userid
FROM tbusiness_pawn_ticket a LEFT JOIN tbusiness_evaluation_report b on a.reportid = b.id 
where (b.userid = userid or b.inviteid= userid or b.state3userid = userid) 
and a.state in (1,3)

然后再通过程序来计算值。

但是感觉量一上去拖慢速度,几百几千条时呢

打算想想是否可以一句sql解决,一开始写的时候一直找不到感觉,写不出来,打算曲线救国,写个储存过程吧:

所以有了方法2:

DROP PROCEDURE IF EXISTS calc_dstx; — 如果存在此名的存储过程,先删除

CREATE PROCEDURE calc_dstx(IN userid INT,OUT amount FLOAT) — 创建名为store_procedure的存储过程 待收本息

BEGIN — 开始
DECLARE done INT DEFAULT -1;
DECLARE temp FLOAT DEFAULT 0;
DECLARE t_interestamount FLOAT DEFAULT 0;
  DECLARE t_state1amount FLOAT DEFAULT 0;
DECLARE
t_state1rate FLOAT DEFAULT 0;
DECLARE t_state2amount FLOAT DEFAULT 0;
DECLARE t_state2rate FLOAT DEFAULT 0;  
DECLARE
t_state3amount FLOAT DEFAULT 0;
DECLARE t_state3rate FLOAT DEFAULT 0;
DECLARE t_platformuseamount2 FLOAT DEFAULT 0;  
DECLARE t_userid INT DEFAULT 0;  
DECLARE t_inviteid INT DEFAULT 0;  
DECLARE t_state3userid INT DEFAULT 0;  
DECLARE report CURSOR FOR select b.interestamount,b.state1amount,b.state1rate,
b.state2amount,b.state2rate,b.state3amount,b.state3rate,
b.platformuseamount2,b.userid,b.inviteid,b.state3userid
FROM tbusiness_pawn_ticket a LEFT JOIN tbusiness_evaluation_report b on a.reportid = b.id 
where (b.userid = userid or b.inviteid= userid or b.state3userid = userid) 
and a.state in (1,3);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; — 如果没有记录,错误处理no data to fetch条件,几乎所有的游标都要用到
SET done=0;
SET temp=0;
OPEN report;
my_loop: LOOP
FETCH report INTO t_interestamount,t_state1amount,t_state1rate,t_state2amount,t_state2rate,t_state3amount,t_state3rate,t_platformuseamount2,t_userid,t_inviteid,t_state3userid;
IF done=1 THEN
LEAVE my_loop;
END IF;
IF userid=t_userid THEN

SET temp = temp + t_state1amount + t_interestamount*t_state1rate – t_platformuseamount2*t_state1rate;
END IF;
IF userid = t_inviteid THEN
SET temp = temp + t_state2amount+t_interestamount*t_state2rate – t_platformuseamount2*t_state2rate;
END IF;
IF userid = t_state3userid THEN
SET temp = temp + t_state3amount+t_interestamount*t_state3rate – t_platformuseamount2*t_state3rate;
END IF;
END LOOP my_loop;
CLOSE report;
— DEALLOCATE report;
SET amount=temp;
END; — 结束

— DEMILITER ; — 恢复;为分隔符


写完之后自信心爆棚,灵感爆发,一口气写出了第三种方法,一条sql

方法3:

select FORMAT(sum(money),2) as total from (
select b.userid as moneyuserid,(b.state1amount + b.interestamount*b.state1rate – b.platformuseamount2*b.state1rate) as money from tbusiness_pawn_ticket a LEFT JOIN tbusiness_evaluation_report b on a.reportid = b.id 
where b.userid = 1080
and a.state in (1,3)
UNION ALL select d.inviteid as moneyuserid,(d.state2amount + d.interestamount*d.state2rate – d.platformuseamount2*d.state2rate) as money from tbusiness_pawn_ticket c LEFT JOIN tbusiness_evaluation_report d on c.reportid = d.id 
where d.inviteid = 1080
and c.state in (1,3)
UNION ALL select f.state3userid as moneyuserid,(f.state3amount + f.interestamount*f.state3rate – f.platformuseamount2*f.state3rate) as money from tbusiness_pawn_ticket e LEFT JOIN tbusiness_evaluation_report f on e.reportid = f.id 
where f.state3userid = 1080 and e.state in (1,3)) as totaltabale

途中碰到几个问题,关于储存过程的循环跳出,关于mysql的全连接需要使用union all,关于集合做表查询必须有个别名。

种种还是基础不够扎实。

先记录下这个,对于这三种效率问题,还没有深入研究,等空余时间深入研究下


本文由【waitig】发表在等英博客
本文固定链接:mysql下的多一表多结果的联合查询外加求和
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)