创建视图遇#1349错误：FROM子句含子查询，求解决办法

你猜的完全没错——MySQL确实不允许视图的FROM子句（包括关联JOIN时用到的子查询）中包含子查询，这是它视图机制的一个历史限制，哪怕你的子查询是嵌套在JOIN里的，也会被判定为违反规则，所以才触发了#1349 - View's SELECT contains a subquery in the FROM clause这个报错。

另外先提个小细节：你原SQL里的AS value, FROM users多了个多余的逗号，这个也要去掉，不然即使解决了子查询的问题，也会触发语法错误。

下面给你两种可行的解决办法，适配不同的MySQL版本：

方法一：用CTE（适用于MySQL 8.0及以上版本） #

MySQL 8.0开始支持在视图中使用公共表表达式（CTE），我们可以把原来JOIN里的两个子查询转换成CTE，这样就避开了FROM子句中子查询的限制：

CREATE VIEW view_user_data AS
WITH join1 AS (
    SELECT a.uid AS id, ROUND(SUM(a.value * a.multiplier), 0) AS value1
    FROM table1 AS a
    GROUP BY a.uid
),
join2 AS (
    SELECT b.uid AS id, ROUND(SUM(b.value * b.multiplier), 0) AS value2
    FROM table2 AS b
    GROUP BY b.uid
)
SELECT 
    u.uid AS uid, 
    u.name AS name, 
    (COALESCE(join1.value1, 0) + COALESCE(join2.value2, 0)) AS total_value,
    (SELECT value FROM table3 WHERE value > total_value LIMIT 1) - 1 AS value
FROM users AS u
LEFT OUTER JOIN join1 ON u.uid = join1.id
LEFT OUTER JOIN join2 ON u.uid = join2.id
ORDER BY uid;

方法二：创建中间视图（兼容所有MySQL版本） #

如果你的MySQL版本低于8.0，没法用CTE，那可以先把两个子查询单独做成视图，再在主视图里关联这些中间视图：

1. 先创建第一个中间视图：

CREATE VIEW view_join1 AS
SELECT a.uid AS id, ROUND(SUM(a.value * a.multiplier), 0) AS value1
FROM table1 AS a
GROUP BY a.uid;

2. 再创建第二个中间视图：

CREATE VIEW view_join2 AS
SELECT b.uid AS id, ROUND(SUM(b.value * b.multiplier), 0) AS value2
FROM table2 AS b
GROUP BY b.uid;

3. 最后创建目标视图：

CREATE VIEW view_user_data AS
SELECT 
    u.uid AS uid, 
    u.name AS name, 
    (COALESCE(view_join1.value1, 0) + COALESCE(view_join2.value2, 0)) AS total_value,
    (SELECT value FROM table3 WHERE value > total_value LIMIT 1) - 1 AS value
FROM users AS u
LEFT OUTER JOIN view_join1 ON u.uid = view_join1.id
LEFT OUTER JOIN view_join2 ON u.uid = view_join2.id
ORDER BY uid;

这两种方法都能帮你绕过MySQL视图的子查询限制，成功创建需要的视图。

内容的提问来源于stack exchange，提问作者T. Maxx