mysql - UNION ALL 更多表和来自不同表的附加数据

Question

我有 5 个相同的 MySQL 表，每个表的数据不同但结构相同。

我需要对它们的值求和，我是这样做的:

SELECT
SUM(alltables.Quantity) AS total_qty,
format(SUM(alltables.eur), 2) as total_eur, 
format(SUM(alltables.eur_80), 2) as total_eur_80, 
format(SUM(alltables.eur)*.12, 2) as detrazioni, 
format(SUM(alltables.eur_80)-(SUM(alltables.eur)*.12), 2) as ricevere
FROM (
    SELECT Quantity, eur, eur_80 FROM table_one WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017' UNION ALL
    SELECT Quantity, eur, eur_80 FROM table_two WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017' UNION ALL
    SELECT Quantity, eur, eur_80 FROM table_three WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017' UNION ALL
    SELECT Quantity, eur, eur_80 FROM table_four WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017' UNION ALL
    SELECT Quantity, eur, eur_80 FROM table_five WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017'
) alltables

这计算了客户必须得到的报表，结果非常完美:

然后我制作了一个具有相同结构的调整表，如果出现一些计算错误，我可以添加一些额外的行来调整/修复他们的收入。

所以，我的问题来了:我必须从上面做一个新的 APART 求和，并使用相同的 WHERE CLAUSE 规范自行计算 eur_80 字段，我必须将它求和为 ricevere(SELECT 语句中的计算字段)。

像这样:

我试过使用 JOIN 但没有成功。

Answer 1

您可以将其添加到子选择中。因为您希望在总计中分别显示不同的 和 ，所以我将该列的添加和格式设置移到了更高级别:

SELECT 
  format(andadjust.total_eur, 2) as total_eur, 
  format(andadjust.total_eur_80, 2) as total_eur_80, 
  format(andadjust.detrazioni, 2) as detrazioni, 
  format(andadjust.ricevere, 2) as ricevere,
  format(andadjust.difference, 2) as difference,
  format(andadjust.ricevere + andadjust.difference, 2) as adjustment
FROM (
  SELECT
    SUM(alltables.Quantity) AS total_qty,
    SUM(alltables.eur) as total_eur, 
    SUM(alltables.eur_80) as total_eur_80, 
    SUM(alltables.eur)*.12 as detrazioni, 
    SUM(alltables.eur_80)-(SUM(alltables.eur)*.12) as ricevere,
    (SELECT sum(eur_80) FROM table_adjustment WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017') as difference
  FROM (
      SELECT Quantity, eur, eur_80 FROM table_one WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017' UNION ALL
      SELECT Quantity, eur, eur_80 FROM table_two WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017' UNION ALL
      SELECT Quantity, eur, eur_80 FROM table_three WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017' UNION ALL
      SELECT Quantity, eur, eur_80 FROM table_four WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017' UNION ALL
      SELECT Quantity, eur, eur_80 FROM table_five WHERE user_id = 849 AND Trimestre_month = '6' AND Trimestre_year = '2017'
  ) alltables
) andadjust