mysql - 根据季度定义添加每月值

Question

尝试根据哪个月份属于哪个季度的定义来添加每月值(因此可以更新定义):

我尝试使用内连接来实现但失败了:

SELECT qr, sum(price)
INTO Table3
FROM Table1
INNER JOIN Table2
on Table1.mth = Table2.mth
INNER JOIN Table3
on Table2.qr = Table3.qr

另外，我不确定 Table1 (qr) 中的附加列是否会让事情变得更容易？

Answer 1

如果您的 table3 已经有这 4 行作为开始，并且您只想更新 table3 中的价格以反射(reflect)每个季度的价格，您可以使用以下查询:

UPDATE Table3 T3, 
  (SELECT T2.qr,sum(T1.price) as quarterly_price
   FROM Table1 T1
   INNER JOIN Table2 T2 ON T1.mth = T2.mth
   GROUP BY T2.qr) AS Result
SET T3.price = Result.quarterly_price
WHERE T3.qr = Result.qr;

sqlfiddle to Update Table3

如果 table3 没有这 4 行并且您想要插入它们，请使用以下查询:

INSERT INTO Table3
  SELECT T2.qr,sum(T1.price) as quarterly_price
   FROM Table1 T1
   INNER JOIN Table2 T2 ON T1.mth = T2.mth
   GROUP BY T2.qr;

sqlfiddle to Insert into Table3

编辑:我对您的代码进行了一些修改，这在 Access 的查询中运行良好:

SELECT Table2.qr,sum(Table1.price) as Price_new
INTO Table3
FROM Table1
INNER JOIN Table2 ON Table1.mth = Table2.mth
GROUP BY Table2.qr;