关于前后端多表联合逻辑计算更新问题

为什么要写这篇文章

问题来源:网友问题求助

相信你也会在工作中遇到各种问题,不管你是用什么语言开发,其实都大同小异。
今天说的这位网友也是如此,他遇到到问题是关于多表联合计算更新数量的问题。

问题的描述:多表计算UPDATE

表1:a_id, a_sum(总成本), a_peo(总人数),
表2:b_id, b_money(人均成本/日)a_id
表3:c_id,c_num(个人的总天数,每个人不一样) 这个c_num是通过表内两个日期相减而得到的字段。a_id

想求这个总成本,因为每个人的时间不一样 所以不知道该怎么去计算这个所有人加在一起的总成本,再传到数据库中的总成本字段里去。
要怎么来实现 sql呢。

问题分析:多表计算如何操作

我们大概分析下,就是说有三张表,要通过一条sql语句来操作这三张表,并从中计算总成本,然后把数值更新到其中一张表中。
这样的话,这三张表就得要用关联字段才好操作。发现问题描述中肯定是缺点东西。所以我们在建立数据表时要注意。

下面我们先建这三张表:

表一table_1:(项目总成本总人数信息表)

作用:用来存放计算后的总成本和这个项目的总人员数

/*
 Navicat Premium Data Transfer

 Source Server         : 本地mysql
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 24/11/2021 12:22:37
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for table_1
-- ----------------------------
DROP TABLE IF EXISTS `table_1`;
CREATE TABLE `table_1`  (
  `a_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `a_sum` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `a_peo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`a_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of table_1
-- ----------------------------
INSERT INTO `table_1` VALUES (1, '0', '0');
INSERT INTO `table_1` VALUES (2, '0', '0');

SET FOREIGN_KEY_CHECKS = 1;


表一table_2:(项目人均成本/日 信息表)

作用:用来存放这个项目 人均成本/日

/*
 Navicat Premium Data Transfer

 Source Server         : 本地mysql
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 24/11/2021 12:22:45
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for table_2
-- ----------------------------
DROP TABLE IF EXISTS `table_2`;
CREATE TABLE `table_2`  (
  `b_id` int(11) NOT NULL,
  `b_money` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `a_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`b_id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of table_2
-- ----------------------------
INSERT INTO `table_2` VALUES (1, '400', 1);
INSERT INTO `table_2` VALUES (2, '500', 2);

SET FOREIGN_KEY_CHECKS = 1;

表一table_3:(项目人员及工时信息表)

作用:用来存放这个项目 个人的总工时天数

/*
 Navicat Premium Data Transfer

 Source Server         : 本地mysql
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 24/11/2021 12:22:53
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for table_3
-- ----------------------------
DROP TABLE IF EXISTS `table_3`;
CREATE TABLE `table_3`  (
  `c_id` int(11) NOT NULL,
  `c_num` int(11) NULL DEFAULT NULL,
  `b_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Fixed;

-- ----------------------------
-- Records of table_3
-- ----------------------------
INSERT INTO `table_3` VALUES (1, 2, 1);
INSERT INTO `table_3` VALUES (2, 2, 1);
INSERT INTO `table_3` VALUES (3, 3, 2);
INSERT INTO `table_3` VALUES (4, 4, 2);

SET FOREIGN_KEY_CHECKS = 1;

下面我们来看看我们新建的三张表:
表table_1:
在这里插入图片描述
表table_2:
在这里插入图片描述

表table_3:
在这里插入图片描述

实现代码:MYSQL实现

下面我们开始写sql了:

常量设置:

我们为了方便,for就不在sql中写,所以我们一次更新一个项目的相关数据。
我们先设置一个常量:

SET @aid=1

在这里插入图片描述

然后我来查看下这个常量有没有设置好:

SELECT @aid

在这里插入图片描述
@aid=1 说明我们的常量已经设置好了。

计算相关的数值:

下面我们计算项目a_id为1项目的总成本

SELECT * 
FROM(
	SELECT
		sum(b.b_money*c.c_num) as table_sum
	FROM
		table_1 as a
	INNER JOIN table_2 as b ON b.a_id = a.a_id
	INNER JOIN table_3 as c ON c.b_id = b.b_id
	WHERE
		a.a_id = @aid
) as table_4

WHERE 
	table_4.table_sum > 0

代码输出:
在这里插入图片描述
下面来计算总项目人员数

SELECT * 
FROM(
	SELECT
		COUNT(c.c_id) as table_sum
	FROM
		table_1 as a
	INNER JOIN table_2 as b ON b.a_id = a.a_id
	INNER JOIN table_3 as c ON c.b_id = b.b_id
	WHERE
		a.a_id = @aid
) as table_5

WHERE 
	table_5.table_sum > 0

代码输出:
在这里插入图片描述
这里我们已经计算到了项目a_id=1的项目总成本为1600,项目总人员数为2,但发现这是两条sql语句,不方便一次执行。
下面我们就讲讲怎么写成一条sql.

更新计算的值

sql更新操作是用UPDATE ,那要怎么更新呢?

UPDATE table_1 as a
SET

a.a_sum = 1600,

a.a_peo = 1

WHERE
	a.a_id = @aid

上面是我们正常操作的sql,发现只要执行,就会把数据写入库中。
现在发现,我们有三条sql语句了,只有这条都执行完才能完成一次计算更新操作。

三条sql语句

SELECT * 
FROM(
	SELECT
		sum(b.b_money*c.c_num) as table_sum
	FROM
		table_1 as a
	INNER JOIN table_2 as b ON b.a_id = a.a_id
	INNER JOIN table_3 as c ON c.b_id = b.b_id
	WHERE
		a.a_id = @aid
) as table_4

WHERE 
	table_4.table_sum > 0
SELECT * 
FROM(
	SELECT
		COUNT(c.c_id) as table_sum
	FROM
		table_1 as a
	INNER JOIN table_2 as b ON b.a_id = a.a_id
	INNER JOIN table_3 as c ON c.b_id = b.b_id
	WHERE
		a.a_id = @aid
) as table_5

WHERE 
	table_5.table_sum > 0
UPDATE table_1 as a
SET

a.a_sum = 1600,

a.a_peo = 1

WHERE
	a.a_id = @aid

我们要怎么将上面三sql条合成一条sql来执行呢?

我们观察下,我们是要把第一条和第二条的结果给a.a_suma.a_peo
其他上面两条代码的结果就只有一值,这是不是告诉我们,可以把这个值给到a.a_suma.a_peo
答案是肯定的,我们可以给他,但怎么给,下面直接看代码。

UPDATE table_1 as a,table_2 as b,table_3 as c
SET
a.a_sum = (
SELECT * 
FROM(
	SELECT
		sum(b.b_money*c.c_num) as table_sum
	FROM
		table_1 as a
	INNER JOIN table_2 as b ON b.a_id = a.a_id
	INNER JOIN table_3 as c ON c.b_id = b.b_id
	WHERE
		a.a_id = @aid
) as table_4

WHERE 
	table_4.table_sum > 0
),

a.a_peo = (
SELECT * 
FROM(
	SELECT
		COUNT(c.c_id) as table_sum
	FROM
		table_1 as a
	INNER JOIN table_2 as b ON b.a_id = a.a_id
	INNER JOIN table_3 as c ON c.b_id = b.b_id
	WHERE
		a.a_id = @aid
) as table_5

WHERE 
	table_5.table_sum > 0
)

WHERE
	a.a_id = @aid

执行这条sql前我们先看下表table_1中的数据,以方便对比,
在这里插入图片描述
然后我们输出代码执行结果:
在这里插入图片描述
我们再去表table_1中看看数据有没有更新:
在这里插入图片描述
我们发现数据已经更新入库了。a_sum=1600就是上面第一条sql执行的结果,a_peo=2就是第二条sql执行的结果。

测试输出:过程结果打印

为了测试,我们可以向表table_2和表table_3加点数据,然后再运行下。可直接运行@aid=2.
下面我们测试下@aid=2;
设置常量:

SET @aid=2

查看常量:

SELECT @aid

开始计算更新入库:

UPDATE table_1 as a,table_2 as b,table_3 as c
SET
a.a_sum = (
SELECT * 
FROM(
	SELECT
		sum(b.b_money*c.c_num) as table_sum
	FROM
		table_1 as a
	INNER JOIN table_2 as b ON b.a_id = a.a_id
	INNER JOIN table_3 as c ON c.b_id = b.b_id
	WHERE
		a.a_id = @aid
) as table_4

WHERE 
	table_4.table_sum > 0
),

a.a_peo = (
SELECT * 
FROM(
	SELECT
		COUNT(c.c_id) as table_sum
	FROM
		table_1 as a
	INNER JOIN table_2 as b ON b.a_id = a.a_id
	INNER JOIN table_3 as c ON c.b_id = b.b_id
	WHERE
		a.a_id = @aid
) as table_5

WHERE 
	table_5.table_sum > 0
)

WHERE
	a.a_id = @aid

执行前先看看表1数据;
执行完界面:
在这里插入图片描述
查看表1中的数据有没有更新:
在这里插入图片描述
我们发现数据已经更新,最后我们人工计算下,看看是不是对的。

问题总结:举一反三

最后简单总结下,其实在开发中,我们都会先写sql,再写其他业务逻辑代码,这要只要sql调试没问题,肯定是有正确的结果的。类似这种多表联合,并带计算和更新的操作,在实际项目中经常出现,我们只需要先简单,再给易变复杂的去拆解目标,这样就可以完成我们想要的事情,达到预期的结果。

  • 6
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 17
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 17
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

huidaoli

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值