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

最新推荐文章于 2022-02-16 22:42:25 发布

huidaoli

最新推荐文章于 2022-02-16 22:42:25 发布

阅读量3.6k

点赞数 6

分类专栏： PHP后端开发文章标签：经验分享后端 sql

本文链接：https://blog.csdn.net/huidaoli/article/details/121512881

版权

PHP后端开发专栏收录该内容

7 篇文章 6 订阅

订阅专栏

为什么要写这篇文章

问题来源：网友问题求助

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

问题的描述：多表计算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_sum和a.a_peo
其他上面两条代码的结果就只有一值，这是不是告诉我们，可以把这个值给到a.a_sum和a.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调试没问题，肯定是有正确的结果的。类似这种多表联合，并带计算和更新的操作，在实际项目中经常出现，我们只需要先简单，再给易变复杂的去拆解目标，这样就可以完成我们想要的事情，达到预期的结果。

huidaoli

关注

6
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
17
评论
关于前后端多表联合逻辑计算更新问题

为什么要写这篇文章问题来源：网友问题求助相信你也会在工作中遇到各种问题，不管你是用什么语言开发，其实都大同小异。今天说的这位网友也是如此，他遇到到问题是关于多表联合计算更新数量的问题。问题的描述：多表计算UPDATE表1：a_id, a_sum（总成本）, a_peo（总人数）,表2：b_id, b_money（人均成本/日）a_id表3：c_id,c_num（个人的总天数，每个人不一样）这个c_num是通过表内两个日期相减而得到的字段。a_id想求这个总成本，因为每个人的时间不一.
复制链接

扫一扫