mysql 创建视图提示View‘s SELECT contains a subquery in the FROM clause

msyql5.6创建视图出现1349错误

View's SELECT contains a subquery in the FROM clause

原因是mysql视图不支持子查询。那么只能把一个视图拆解成多个

原来的视图语句：

SELECT
    yue.m AS mon,
    IFNULL( a.sum, 0 ) AS sellValue,
    ifnull( a.sxhs, 0 ) AS sxbs 
FROM
    (
SELECT
    1 AS m UNION ALL
SELECT
    2 UNION ALL
SELECT
    3 UNION ALL
SELECT
    4 UNION ALL
SELECT
    5 UNION ALL
SELECT
    6 UNION ALL
SELECT
    7 
    ) yue
    LEFT JOIN (
SELECT MONTH
    ( CREATEDATE) AS m,
    sum( SXJE ) AS sum,
    count(ID) AS sxhs 
FROM
    table
WHERE
    YEAR ( CREATEDATE ) = 2022
GROUP BY
    MONTH ( CREATEDATE ) 
    ) a ON yue.m = a.m 
ORDER BY
    yue.m ASC

修改后的：

标红的就是两个子查询，那么现在就把子查询拉出来单独当做一个视图。

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW view_re_1 AS SELECT
    1 AS m UNION ALL
SELECT
    2 UNION ALL
SELECT
    3 UNION ALL
SELECT
    4 UNION ALL
SELECT
    5 UNION ALL
SELECT
    6 UNION ALL
SELECT
    7 

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW view_re_2 AS SELECT MONTH
    ( CREATEDATE) AS m,
    sum( SXJE ) AS sum,
    count(ID) AS sxhs 
FROM
    table
WHERE
    YEAR ( CREATEDATE ) = 2022
GROUP BY
    MONTH ( CREATEDATE ) 

最后创建视图的语句：

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW view_re   AS SELECT
    yue.m AS mon,
    IFNULL( a.sum, 0 ) AS sellValue,
    ifnull( a.sxhs, 0 ) AS sxbs 
FROM
 view_re_1  yue
    LEFT JOIN view_re_2  a ON yue.m = a.m 
ORDER BY
    yue.m ASC