mysql - MYSQL 计算值

Question

我有 3 张桌子

1.salary_earning

CREATE TABLE `salary_earning` (
  `id` int NOT NULL AUTO_INCREMENT,
  `basic_salary` int NOT NULL,
  `health_allowance` int NOT NULL,
  `transport_allowance` int NOT NULL,
  `overtime_allowance` int NOT NULL,
  `leave_encashment` int NOT NULL,
  `accomodation_allowance` int NOT NULL,
  `bonus_allowance` int NOT NULL,
  `emp_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

INSERT INTO `salary_earning` (`id`, `basic_salary`, `health_allowance`, `transport_allowance`, `overtime_allowance`, `leave_encashment`, `accomodation_allowance`, `bonus_allowance`, `emp_id`, `date`) VALUES
(1, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 'DO-123', '2020-10-02'),
(2, 1000, 200, 100, 50, 300, 500, 200, 'DO-456', '2020-10-02');

2.salary_deduction

CREATE TABLE `salary_deduction` (
 `id` int NOT NULL AUTO_INCREMENT,
 `income_tax` int NOT NULL,
 `advance_money` int NOT NULL,
 `emp_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `date` date NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

INSERT INTO `salary_deduction` (`id`, `income_tax`, `advance_money`, `emp_id`, `date`) VALUES
(2, 1000, 1000, 'DO-123', '2020-10-02'),
(3, 200, 300, 'DO-456', '2020-10-02');

3.salary_final

CREATE TABLE `salary_final` (
 `id` int NOT NULL AUTO_INCREMENT,
 `emp_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `date` date NOT NULL,
 `total_earning` int NOT NULL,
 `total_deduction` int NOT NULL,
 `final_salary` int NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

数据被插入到收入（tbl1）和扣除（tbl2）中。我希望salary_final 表根据其他2 个表的独特emp_id 计算“final_salary = total_earning - total_deduction”并填充第三个表。

我是 SQL 新手，我是否通过创建新表以正确的方式进行操作？或者仅仅创建 mysqview 就足以满足这种要求？

谢谢。

标签： mysql