如何解决MySQL中'视图的SELECT在FROM子句中包含子查询”？

View 的 FROM 子句中的子查询故障排除

MySQL 视图的 SELECT 语句出现“View's SELECT contains a subquery in the FROM Clause”错误FROM 子句中包含子查询。根据 MySQL 文档，这是不允许的。

要在创建名为 view_credit_status 的视图的查询中解决此问题，请考虑以下解决方案：

有问题的子查询是：

(select credit_usage.client_id, 
        sum(credits_used) as credits_used 
 from credit_usage 
 group by credit_usage.client_id) as t0

为此子查询创建一个单独的视图：

create view view_credit_usage_summary as 
select credit_usage.client_id, 
       sum(credits_used) as credits_used 
from credit_usage 
group by credit_usage.client_id

在 view_credit_status 中视图，引用新的 view_credit_usage_summary 视图而不是子查询：

create view view_credit_status as 
(select credit_orders.client_id, 
        sum(credit_orders.number_of_credits) as purchased, 
        ifnull(view_credit_usage_summary.credits_used,0) as used 
 from credit_orders
 left outer join view_credit_usage_summary on view_credit_usage_summary.client_id = credit_orders.client_id
 where credit_orders.payment_status='Paid'
 group by credit_orders.client_id)

这种方法将子查询分离到专用视图中，允许您在不违反 MySQL 限制的情况下访问其结果。

以上是如何解决MySQL中'视图的SELECT在FROM子句中包含子查询”？的详细内容。更多信息请关注PHP中文网其他相关文章！