mysql - View 中的 SUM(table2.value * table2.value) (+ table1.value)

Question

我在搜索引擎中使用以下 View :

CREATE VIEW msr_joined_view AS 
SELECT table1.id AS msr_id, table1.msr_number, table1.overview, 
SUM(table2.quantity * table2.unit_price) AS grand_total 
FROM table1 
INNER JOIN table2 ON table1.id = table2.msr_id 
GROUP BY table1.msr_number;

这让我输出如下:

msr_id    msr_number    overview    grand_total
------    ----------    ---------   -----------
 1          4            stuff       100.00
 2          5            other       15.00
 3          7            more        17.95

我现在需要引入一个名为 taxes_shipping 的列，该列存在于 table1 中。我需要将该列的值添加到每行的 grand_total 中。我如何修改我的 View 来做到这一点？

表结构:

table1   has many  table2
------             ------ 
  id                msr_id(FK)
  msr_number        unit_price
  overview          quantity
  taxes_shipping

Answer 1

您可以在 SELECT 中添加另一列，例如:

CREATE VIEW msr_joined_view AS 
SELECT table1.id AS msr_id, table1.msr_number, table1.overview, 
SUM(table2.quantity * table2.unit_price) + (SELECT SUM(taxes_shipping) FROM table1 t1 WHERE id = table1.id) 
FROM table1 
INNER JOIN table2 ON table1.id = table2.msr_id 
GROUP BY table1.msr_number;