用于按月添加/减去值和分组的MySQL查询

Question

我有3表:2个表和1个视图。我没有得到正确的结果。

1表

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` VALUES
(1, 100, 20, 15, 10, 5, 30, 5, 101, '2020-10-10');

INSERT INTO `salary_earning` VALUES
(2, 0, 0, 0, 0, 0, 0, 30, 101, '2020-10-11');

INSERT INTO `salary_earning` VALUES
(3, 100, 20, 15, 10, 5, 30, 5, 102, '2020-11-10');

2表

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` VALUES
(1, 12, 30, '101', '2020-10-10');

3视图

SELECT 
    slr.*,
    `total_earning` - `total_deduction` AS `final_salary`
FROM (
  SELECT 
    `se`.`emp_id`,
    `se`.`date`,
    (
        `basic_salary`+
        `health_allowance`+
        `transport_allowance`+
        `overtime_allowance`+
        `leave_encashment`+
        `accomodation_allowance`+
        `bonus_allowance`
    ) AS `total_earning`,
    IFNULL(`income_tax` + `advance_money`, 0) AS `total_deduction`
  FROM `salary_earning` `se`
  LEFT JOIN `salary_deduction` `sd` ON 
    `se`.`emp_id` = `sd`.`emp_id` AND `se`.`date` = `sd`.`date`
) slr;

电流输出

+--------+------------+---------------+-----------------+--------------+
| emp_id |       date | total_earning | total_deduction | final_salary |
+--------+------------+---------------+-----------------+--------------+
|    101 | 2020-10-10 |           185 |              42 |          143 |
|    101 | 2020-10-11 |            30 |               0 |           30 |
|    102 | 2020-11-10 |           185 |               0 |          185 |
+--------+------------+---------------+-----------------+--------------+

想要的输出/我想要的

+--------+------------+---------------+-----------------+--------------+
| emp_id |       date | total_earning | total_deduction | final_salary |
+--------+------------+---------------+-----------------+--------------+
|    101 | Oct 20     |           215 |              42 |          173 |
|    102 | Nov 20     |           185 |               0 |          185 |
+--------+------------+---------------+-----------------+--------------+

因此，如果在相同的emp_id中添加了一个新的值(在收入或扣减项下)，我想要一个完整月的汇总。你能帮我做错事吗？

https://sqlize.online/

Answer 1

​

为了做到这一点，您需要使用日期格式，您可以查看更多的这里。

示例：

• 使用%m获取月份的值。例：01为一月
• 使用%M获取月份的值名称。例：1月

Answer 2

解决方案非常简单(SQLize.online)：

您只需要通过格式化的date字段来聚合数据，比如：

SELECT -- select aggregate data
    `slr`.`emp_id`, 
    `slr`.`date`,
    SUM(`total_earning`) `total_earning`,
    SUM(`total_deduction`) `total_deduction`,
    SUM(`total_earning` - `total_deduction`) AS `final_salary`
FROM (
  SELECT 
    `se`.`emp_id`,
    DATE_FORMAT(`se`.`date`, '%M %y') date, -- format date as month - year
    (
        `basic_salary`+
        `health_allowance`+
        `transport_allowance`+
        `overtime_allowance`+
        `leave_encashment`+
        `accomodation_allowance`+
        `bonus_allowance`
    ) AS `total_earning`,
    IFNULL(`income_tax` + `advance_money`, 0) AS `total_deduction`
  FROM `salary_earning` `se`
  LEFT JOIN `salary_deduction` `sd` ON 
    `se`.`emp_id` = `sd`.`emp_id` AND `se`.`date` = `sd`.`date`
) slr
GROUP BY `slr`.`emp_id`, `slr`.`date`; -- group by emp_id and formatted date