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

习惯用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;