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