mysql - 从具有多个子查询/联接的 Select 语句创建 View

Question

所以我有多个表，我正在尝试创建一个 View 来显示我的 select 语句中返回的所有内容。然而，mysql 似乎不允许创建在 from 中具有子查询的 View 。

所以不太确定如何将其转换为 View 。

Select  Title as "AlbumName" , 
        Sum(trk1.Price * trk1.Quant) as "TotalSales"
From    Album alb
        INNER JOIN
        (
            SELECT  AlbumId, 
                    t1.UnitPrice as "Price" , 
                    t1.Quantity as "Quant"
            FROM    Track trk
                    INNER JOIN
                    (
                        SELECT  TrackId, UnitPrice, Quantity
                        FROM    InvoiceLine
                        WHERE   InvoiceId IN ( SELECT InvoiceId FROM Invoice )
                    ) AS t1 ON (trk.TrackId = t1.TrackId)
        ) as trk1 ON (alb.AlbumId = trk1.AlbumId)
Group By alb.AlbumId

Answer 1

在 MySQL 中创建一个 VIEW 很简单，但是有一些限制。 See HERE: MySQL View

限制之一是 VIEW 不能有在 FROM 子句中包含子查询的 SELECT 语句。因此，作为替代方案，首先为包含 IN 子句的子查询创建一个 View 。

CREATE VIEW InvoiceLineView
AS
SELECT  DISTINCT a.*
FROM    InvoiceLine a
        INNER JOIN Invoice b
            ON a.InvoiceId = b.InvoiceId;

创建 View 后，您现在可以通过从原始查询加入 View 来继续，这样您现在就可以创建一个完全可用的 VIEW。对于未计算的子查询，您不需要额外的 View 。

CREATE VIEW AlbumSales
AS
Select  Title as AlbumName, 
        Sum(t1.UnitPrice * t1.Quantity) as TotalSales
From    Album alb
        INNER JOIN Track trk
            ON alb.AlbumId = trk1.AlbumId
        INNER JOIN InvoiceLineView t1
            ON trk.TrackId = t1.TrackId
Group   By alb.AlbumId;

---

不过，MySQL 中的 VIEWS 很糟糕。

• MySQL VIEW as performance troublemaker