mysql创建视图时出现 #1349 - View's SELECT contains a subquery in the FROM clause

[giopan]

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

[CT_LXL]

引用 楼主 giopan 的回复:

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;

[giopan]

引用 1 楼 zlloct 的回复:

[quote=引用 楼主 giopan 的回复:]

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;

[/quote] 还是一样的错误哦

[CT_LXL]

引用 2 楼 giopan 的回复:

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;

[Rotel-刘志东]

----拆分一下处理，先创建一个视图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;

[qq_33762427]

mysql 版本在5.6以下 都会有该问题， 请将安装5.7以上版本的就OK

[mylogic]

确实如此， -------------------------------------------------------------------- “”凭什么非叫我输6个字符？别人的回复我连个点赞的地方都没有，觉得有用还得回6个字符，CSDN的产品经理有病”