mysql 创建视图出现1349 View's SELECT contains a subquery in the FROM clause解决办法

最新推荐文章于 2024-10-01 09:07:29 发布

simplecnpay

最新推荐文章于 2024-10-01 09:07:29 发布

阅读量4.8w

点赞数 4

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/simplecnpay/article/details/39251667

本文介绍了一种在MySQL中创建视图的方法，特别是如何解决带有子查询的视图创建问题。通过将复杂的子查询拆分为多个独立视图再进行关联查询的方式，成功避免了MySQL不支持子查询的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

习惯用oracle创建视图，最近在mysql下创建视图出现以下错误：

查询：CREATE or replace VIEW view_order_summary AS select a.opuser,a.audit_status,a.total,b.atotal,round(a.total/b.atotal,2) rate from...

错误代码： 1349
View's SELECT contains a subquery in the FROM clause

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.002 sec

原因是mysql不支持子查询，现解决办法就是把子查询分成多个视图，然后再把其它视图当表一样关联查询。

原出错语句：

CREATE OR REPLACE VIEW view_back_summary AS
SELECT a.opuser,a.audit_status,a.total,b.atotal,ROUND(a.total/b.atotal,2) rate FROM (
SELECT t.`OPUSER`,t.`AUDIT_status`,COUNT(1) total FROM back_form t GROUP BY t.`AUDIT_status`,t.`OPUSER`) a ,(
SELECT o.`OPUSER`,COUNT(1) atotal FROM back_form o GROUP BY o.`OPUSER`) b WHERE a.opuser = b.opuser;

修改后的语句：

CREATE VIEW view_back_1 AS
SELECT t.`OPUSER`,t.`AUDIT_status`,COUNT(1) total FROM back_form t GROUP BY t.`AUDIT_status`,t.`OPUSER`;

CREATE VIEW view_back_2 AS
SELECT o.`OPUSER`,COUNT(1) atotal FROM back_form o GROUP BY o.`OPUSER`;

CREATE OR REPLACE VIEW view_back_summary AS
SELECT a.opuser,a.audit_status,a.total,b.atotal,ROUND(a.total/b.atotal,2) rate FROM view_back_1 a, view_back_2 b WHERE a.opuser = b.opuser;

SELECT * FROM view_back_summary;