MySQL:SUM函数应用于另一个查询选择的字段中包含的公式

Question

我需要执行 select SUM()，其中这是包含在另一个查询选择的字段中的公式。示例:

table_A(“公式”字段在每个单元格中包含一个涉及表 B 中的列的算术表达式):

+------------+--------------+------------+
| Product_id | related_prod |  formula   |
+------------+--------------+------------+
| U1         | C2           | col2-col1  |
| U2         | C3           | col3-col2  |
| U3         | C4           | col3-col1  |
+------------+--------------+------------+

表_B:

+------------+---------+------------+----------+------+------+------+
| Product_id | year_id | company_id | month_id | col1 | col2 | col3 |
+------------+---------+------------+----------+------+------+------+
| C2         |    2017 |          1 |        2 |  100 |  200 |  300 |
| C3         |    2017 |          1 |        2 |  400 |  500 |  600 |
| C4         |    2017 |          1 |        2 |  700 |  800 |  900 |
+------------+---------+------------+----------+------+------+------+

然后，我执行以下查询:

SELECT 
SUM(totals.relaz) as final_sum, 
totals.relaz as 'col', 
totals.prod as 'prod', 
totals.cons as 'cons', 
m.company_id, m.month_id, m.year_id, FROM `table_B` m, 
    ( SELECT formula as relaz, 
     related_prod as prod,
     p.product_id as cons FROM table_A p  ) 
     AS totals 
WHERE m.product_id=totals.prod 
GROUP BY m.company_id, m.year_id, m.month_id, m.product_id, totals.cons

选择之后，我确实希望考虑到例如唯一的产品“U1”，相应的行将是

+-----------+-----------+------+------+------------+----------+---------+
| final_sum |    col    | prod | cons | company_id | month_id | year_id |
+-----------+-----------+------+------+------------+----------+---------+
|       100 | col2-col1 | C2   | U1   |          1 |        2 |    2017 |
+-----------+-----------+------+------+------------+----------+---------+

相反，我得到的是

+-----------+-----------+------+------+------------+----------+---------+
| final_sum |    col    | prod | cons | company_id | month_id | year_id |
+-----------+-----------+------+------+------------+----------+---------+
|         0 | col2-col1 | C2   | U1   |          1 |        2 |    2017 |
+-----------+-----------+------+------+------------+----------+---------+

即尽管“col”字段包含正确的方程，但 Final_sum 字段始终设置为 0。

我做错了什么？

提前谢谢您亚历克斯

Answer 1

您正在尝试从字符串列(table_A.formula)获取总和。这将导致 0。MySQL/MariaDB 不会尝试将字符串转换为列引用并计算字符串中的公式。

另一件事是，您应该列出不在 GROUP BY 中的聚合函数中的所有列。

要获得您想要的结果，请使用:

SELECT 
  SUM(CASE 
      WHEN a.formula = 'col2-col1' THEN b.col2-b.col1 
      WHEN a.formula = 'col3-col1' THEN b.col3-b.col1 
      WHEN a.formula = 'col3-col2' THEN b.col3-b.col2
     END
     ) AS final_sum, 
  a.formula as 'col', 
  a.related_prod as 'prod', 
  a.Product_id as 'cons', 
  b.company_id, 
  b.month_id, 
  b.year_id
FROM table_B b
  JOIN table_A a on a.related_prod=b.Product_id 
GROUP BY a.formula, a.related_prod, a.Product_id, b.company_id, b.month_id, b.year_id