mysql - 创建 View 时出错( View 的 SELECT 在 FROM 子句中包含子查询)请为这段代码建议替代选项或方法

Question

创建 View 时出现以下错误

View's SELECT contains a subquery in the FROM clause

请为这段代码建议替代选项或方法

Create or Replace view mydb.cnCustomerv
as
Select * from mydb.customermaster 
left join 
(
    Select customertransaction.transactioninvoiceno , customertransaction.transactionvalue , customertransaction.cardnumber from mydb.customertransaction order by customertransaction.transactionid desc limit 1
) as a on 
mydb.customermaster.cardnumber = a.cardnumber
left join
(
    Select customerredemption.transactionuniquecode , customerredemption.redemptionvalue , customerredemption.cardnumber from mydb.customerredemption order by customerredemption.redemptionid desc limit 1
) as B on
mydb.customermaster.cardnumber = B.cardnumber;

Answer 1

根据手册，VIEW不能包含子查询。如果您想在查询上创建VIEW，则需要为子查询创建单独的 View 。

第一次查看

Create or Replace VIEW mydb.CTView
AS 
Select ct.transactioninvoiceno, ct.transactionvalue, ct.cardnumber 
from mydb.customertransaction ct order by ct.transactionid desc limit 1

第二个 View

Create or Replace VIEW mydb.CRView
AS 
Select cr.transactionuniquecode, cr.redemptionvalue, cr.cardnumber 
from mydb.customerredemption cr order by cr.redemptionid desc limit 1

主视图，

Create or Replace view mydb.cnCustomerv
as
Select * from mydb.customermaster 
left join mydb.CTView as a on mydb.customermaster.cardnumber = a.cardnumber
left join mydb.CRView as B on mydb.customermaster.cardnumber = B.cardnumber;

A view definition is subject to the following restrictions :来自MySQL手册

• SELECT 语句的 FROM 子句中不能包含子查询。
• ...