视图的 SELECT 在 FROM 子句中包含一个子查询

我有两个表，我需要创建一个视图。这些表是:

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

我使用以下查询来完成此操作。没有“ create View”部分的查询工作得很好，但是如果使用“ create View”，则会显示错误“ 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)
132969 次浏览
小开
最佳答案

根据文件:

MySQL 文档

  • 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

小开 
create view view_clients_credit_usage as
select client_id, sum(credits_used) as credits_used
from credit_usage
group by client_id


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 view_clients_credit_usage as t1 on t1.client_id = credit_orders.client_id
where credit_orders.payment_status='Paid'
group by credit_orders.client_id)
小开

正如最近关于 浏览限制的 MySQL 文档所说:

在 MySQL 5.7.7之前，子查询不能在视图的 FROM 子句中使用。

这意味着，选择 MySQL v5.7.7或更新的版本，或者将现有的 MySQL 实例升级到这样的版本，将完全消除对视图的这种限制。

但是，如果您有一个早于 v5.7.7的当前生产 MySQL 版本，那么在决定是否升级时，视图限制的去除应该只是评估的标准之一。使用其他答案中描述的变通方法可能是一个更可行的解决方案——至少在短期内是这样的。

小开

在我看来，MySQL 3.6出现以下错误，而 MySQL 3.7不再出错。我还没有在文档中找到任何关于此修复的内容。


提交答案