mysql - 如何合并三个表中的列，并根据月份在一行中显示？

我有三张桌子







我想从monthly_salaries中减去total_salary的每月总和，从扣除额中获得月度的总和，从奖金中获得月度的总和，并在我看来。我只是不知道该怎么做。

我分别尝试了它们，如下所示：

SELECT ANY_VALUE(DATE_FORMAT(date_added, '%m-%Y')), sum(m.total_salary) FROM monthly_salary m GROUP BY month(m.date_added), year(m.date_added);

SELECT ANY_VALUE(DATE_FORMAT(date_added, '%m-%Y')), sum(d.amount) from deductions d GROUP BY month(d.date_added), year(d.date_added);

SELECT ANY_VALUE(DATE_FORMAT(date_added, '%m-%Y')), sum(b.amount) from bonuses b GROUP BY month(b.date_added), year(b.date_added);

我怎样才能将它们结合起来，这样我才能连续获得当月的薪水，奖金和扣除额？

最佳答案

您可以从查询中选择并加入它们。对于这三个结果，如果其中一个表中缺少一个月，则需要完全外部联接。不幸的是，MySQL不支持完整的外部联接。如果保证一个表中永远不会有一个月，而另一个表中绝对不会有一个月，则可以进行内部联接：

SELECT
  CONCAT(LPAD(mnth, 2, 0), '-', yr),
  s.total AS salary,
  d.total AS deduction,
  b.total AS bonus
FROM
(
  SELECT
    month(date_added) AS mnth,
    year(date_added) AS yr,
    sum(total_salary) AS total
  FROM monthly_salary
  GROUP BY month(date_added), year(date_added);
) s
JOIN
(
  SELECT
    month(date_added) AS mnth,
    year(date_added) AS yr,
    sum(amount) AS total
  FROM deductions
  GROUP BY month(date_added), year(date_added);
) d USING (mnth, yr)
JOIN
(
  SELECT
    month(date_added) AS mnth,
    year(date_added) AS yr,
    sum(amount) AS total
  FROM bonuses
  GROUP BY month(date_added), year(date_added);
) b USING (mnth, yr)
ORDER BY yr, mnth;

如果可能缺少月份，请首先选择所有月份，然后在子查询上方保留外部联接：

SELECT
  CONCAT(LPAD(mnth, 2, 0), '-', yr),
  s.total AS salary,
  d.total AS deduction,
  b.total AS bonus
FROM
(
  SELECT month(date_added) AS mnth, year(date_added) AS yr
  FROM monthly_salary
  UNION
  SELECT month(date_added) AS mnth, year(date_added) AS yr
  FROM deductions
  UNION
  SELECT month(date_added) AS mnth, year(date_added) AS yr
  FROM bonuses
) months
LEFT JOIN (...) s USING (mnth, yr)
LEFT JOIN (...) d USING (mnth, yr)
LEFT JOIN (...) b USING (mnth, yr)
ORDER BY yr, mnth;