mysql创建视图报错 1349 View's SELECT contains a subquery in the FROM clause

本文主要是介绍mysql创建视图报错 1349 View's SELECT contains a subquery in the FROM clause，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

报错sql (举例):

CREATE OR REPLACE
VIEW v_tab_b AS
SELECT
   `tab`.`id` AS `id`,
   `tab`.`create_time` AS `create_time`
FROM
( SELECT
           `b`.`id` AS `id`
           `b`.`create_time` AS `create_time`
       FROM
           (
tab_b `b`
           )
       WHERE
           (
b.id is not null
           )
       GROUP BY
           `b`.`id`
   ) `tab`
WHERE
   (`tab`.`id` IS NOT NULL) ;

此时执行报错:

原因:

视图创建的sql语句中在from后面包含了子查询，这在mysql中是不允许的 (见报错sql中加粗部分)

解决方法:

把from后面的子查询单独创建为视图(子视图), 然后将此视图作为另外一个视图(主视图)的 from 部分即可

正确sql:

# 1: 将子查询部分拿出来, 单独创建为一个视图 v_tab_b_sub

CREATE OR REPLACE VIEW `v_tab_b_sub` AS
SELECT
   `b`.`id` AS `id`,
   `b`.`create_time` AS `create_time`
FROM
   (
  tab_b b
   )
WHERE
   (
       b.id is not null
   )
GROUP BY
   `b`.`id`

# 2: 创建视图 v_tab_b, 其中将子视图 v_tab_b_sub 作为from部分即可

CREATE OR REPLACE
VIEW v_tab_b AS
SELECT
   `tab`.`id` AS `id`,
   `tab`.`create_time` AS `create_time`
FROM
v_tab_b_sub as tab
WHERE
   (`tab`.`id` IS NOT NULL)