视图中的SUM(table2.value * table2.value) (+ table1.value)

Question

我在一个搜索引擎中使用了以下视图：

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中。如何修改我的视图才能做到这一点？

表结构：

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;