MySQL如何使用子查询创建视图？

debugcn 发表于 Dev

小猫游行

我想创建一个名为OrderSummary的视图，其中包含CUSTOMER.FirstName，CUSTOMER.LastName，INVOICE.InvoiceDate和发票总额（通过确定INVOICE_ITEM.Quantity的总和乘以SERVICE.ServicePrice计算得出）。我想使用子查询来确定发票总额。谢谢您的帮助。

CREATE VIEW OrderSummaryView AS
    SELECT c.FirstName, c.LastName, i.InvoiceDate
FROM CUSTOMER c, INVOICE i, SERVICE s  
WHERE c.CustomerID = i.InvoiceID  
AND c.CustomerID = s.ServiceID; 
SELECT sum(UnitPrice) * Quantity AS InvoiceTotal
FROM        SERVICE, INVOICE_ITEM
WHERE       SERVICE.ServiceID = invoice_item.InvoiceID

约翰·鲁德尔

您应该首先对select语句进行操作，以包含特定于联接的语法

SELECT c.FirstName, c.LastName, i.InvoiceDate
FROM CUSTOMER c
JOIN INVOICE i ON c.CustomerID = i.InvoiceID 
JOIN INVOICE_ITEM ii ON ii.InvoiceID = i.InvoiceID 
JOIN SERVICE s ON ii.ServiceID = s.ServiceID;

然后我们可以添加您的其他子查询

SELECT c.FirstName, c.LastName, i.InvoiceDate, t.InvoiceTotal
FROM CUSTOMER c
JOIN INVOICE i ON c.CustomerID = i.InvoiceID 
JOIN INVOICE_ITEM ii ON ii.InvoiceID = i.InvoiceID 
JOIN SERVICE s ON ii.ServiceID = s.ServiceID
JOIN
(   SELECT SUM(UnitPrice) * Quantity AS InvoiceTotal, i.InvoiceID
    FROM SERVICE s
    JOIN INVOICE_ITEM i ON i.InvoiceID = s.ServiceID
    GROUP BY i.InvoiceID
) as t ON t.InvoiceID = i.InvoiceID;

看起来有点可疑的一件事是，customerid = invoiceid = serviceid您确定所有这些都是正确的pk-> fk关系吗？

要创建带有子视图的视图，您必须分两个步骤进行操作

CREATE VIEW cust_inv_serv AS 
    SELECT c.FirstName, c.LastName, i.InvoiceDate, i.InvoiceID
    FROM CUSTOMER c
    JOIN INVOICE i ON c.CustomerID = i.InvoiceID 
    JOIN INVOICE_ITEM ii ON ii.InvoiceID = i.InvoiceID 
    JOIN SERVICE s ON ii.ServiceID = s.ServiceID;

CREATE VIEW OrderSummaryView AS 
    SELECT v.FirstName, v.LastName, v.InvoiceDate, v.InvoiceID, SUM(UnitPrice) * Quantity AS InvoiceTotal
    FROM SERVICE s
    JOIN INVOICE_ITEM i ON i.InvoiceID = s.ServiceID
    JOIN cust_inv_serv v ON v.InvoiceID = i.InvoiceID
    GROUP BY i.InvoiceID;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。