View#39;s SELECT contains a subquery in the FROM clause(视图的 SELECT 在 FROM 子句中包含子查询)
问题描述
我有两个表,我需要创建一个视图.这些表格是:
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)
我使用以下查询来执行此操作.没有创建视图"部分的查询运行良好,但使用创建视图"时,它显示错误视图的 SELECT 包含 FROM 子句中的子查询".可能是什么问题&可能的解决方案:
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 子句中包含子查询.
您的解决方法是为每个子查询创建一个视图.
Your workaround would be to create a view for each of your subqueries.
然后从您的视图中访问这些视图 view_credit_status
Then access those views from within your view view_credit_status
这篇关于视图的 SELECT 在 FROM 子句中包含子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:视图的 SELECT 在 FROM 子句中包含子查询


- 更改自动增量起始编号? 2021-01-01
- 使用 Oracle PL/SQL developer 生成测试数据 2021-01-01
- 在SQL中,如何为每个组选择前2行 2021-01-01
- 远程 mySQL 连接抛出“无法使用旧的不安全身份验证连接到 MySQL 4.1+"来自 XAMPP 的错误 2022-01-01
- 如何将 Byte[] 插入 SQL Server VARBINARY 列 2021-01-01
- 如何使用 pip 安装 Python MySQLdb 模块? 2021-01-01
- SQL 临时表问题 2022-01-01
- 如何将 SonarQube 6.7 从 MySQL 迁移到 postgresql 2022-01-01
- 导入具有可变标题的 Excel 文件 2021-01-01
- 以一个值为轴心,但将一行上的数据按另一行分组? 2022-01-01