View的SELECT包含FROM子句中的子查询(View's SELECT contains a subquery in the FROM clause)

View的SELECT包含FROM子句中的子查询(View's SELECT contains a subquery in the FROM clause)

我有两个表，我需要创建一个视图。 表格是：

credit_orders(id, client_id, number_of_credits, payment_status) credit_usage(id, client_id, credits_used, date)

我使用以下查询来做到这一点。 没有“创建视图”部分的查询工作良好，但使用“创建视图”，它显示错误“View的SELECT包含FROM子句中的子查询”。 什么可能是问题和可能的解决方案：

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

I have two tables and I need to create a view. The tables are:

credit_orders(id, client_id, number_of_credits, payment_status) credit_usage(id, client_id, credits_used, date)

I use the following query to do this. The query without the "create view" part works well but with "create view", it shows the error "View's SELECT contains a subquery in the FROM clause". What could be the issue & possible solution:

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

最满意答案

根据文档：

MySQL文档

SELECT语句不能在FROM子句中包含一个子查询。

您的解决方法是为每个子查询创建一个视图。

然后从您的view_credit_status视图中访问这些视图

As per documentation:

MySQL Docs

The SELECT statement cannot contain a subquery in the FROM clause.

Your workaround would be to create a view for each of your subqueries.

Then access those views from within your view view_credit_status