mysql创建视图时出现 #1349 - View's SELECT contains a subquery in the FROM clause
2014-11-07 18:23
CREATE VIEW bm_jifen_all_view AS SELECT a.id, a.gname, b.zj FROM bumen AS a LEFT OUTER JOIN (SELECT SUM(jifen) AS zj, R_bm FROM mybox WHERE iscls =0 GROUP BY bm) AS b ON a.id = b.bm
求大神帮忙,在创建MYSQL视图时出现如下错误:
#1349 - View's SELECT contains a subquery in the FROM clause
回复讨论(解决方案)
CREATE VIEW bm_jifen_all_view AS SELECT a.id, a.gname, b.zj FROM bumen a LEFT OUTER JOIN (SELECT SUM(jifen) AS zj, R_bm FROM mybox WHERE iscls = 0 GROUP BY bm) b ON a.id = b.bm;
CREATE VIEW bm_jifen_all_view AS SELECT a.id, a.gname, b.zj FROM bumen a LEFT OUTER JOIN (SELECT SUM(jifen) AS zj, R_bm FROM mybox WHERE iscls = 0 GROUP BY bm) b ON a.id = b.bm;
还是一样的错误哦
MYSQL的视图不支持子查询.
可以这样:
先创建视图v1,
create view v1 as SELECT SUM(jifen) AS zj, bm, R_bm FROM mybox WHERE iscls = 0 GROUP BY bm;
再用包含子查询的视图v1
create view v2 as SELECT a.id, a.gname, b.zj FROM bumen a LEFT OUTER JOIN v1 as b ON a.id = b.bm;
----拆分一下处理,先创建一个视图a create view a as SELECT SUM(jifen) AS zj, R_bm FROM mybox WHERE iscls =0 GROUP BY bm ---再创建视图b,后在与视图a做连接 create view b as SELECT a.id, a.gname, b.zj FROM bumen m LEFT OUTER JOIN a as n ON m.id = m.bm;