mysql - 如何在一个 View 中从 SQL 中的另一个表求和？

Question

我有两张 table 可维护和二表

第二个表中有一列 Maintable_ID 和 Price。

我在 Workbench 中建立了这样一个 View :

CREATE VIEW test AS 
SELECT Maintable.ID as ID,
       Maintable.Name as Name,
       Maintable.Date as Date,
       Secondtable.Price as Price,
From maintable

LEFT JOIN secondtable
ON Maintable.ID=Secondtable.Maintable_ID

我想过做这样的事情:

SUM(Price) as GPrice FROM Secondtable WHERE Maintable.ID=Secondtable.Maintable_ID

如何找出 Secondtable 中所有项目的总价，其中 Secondtable.Maintable_ID= Maintable.ID

感谢您的帮助。

Answer 1

试试这个..

CREATE VIEW test AS 
SELECT Maintable.ID as ID,
       Maintable.Name as Name,
       Maintable.Date as Date,
       sum (Secondtable.Price) as GPrice,
From maintable

LEFT JOIN secondtable
ON Maintable.ID=Secondtable.Maintable_ID
Group by 
      Maintable.ID as ID,
       Maintable.Name as Name,
       Maintable.Date as Date