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

最新推荐文章于 2024-07-03 03:17:52 发布

marks59848660

最新推荐文章于 2024-07-03 03:17:52 发布

阅读量2.7w

点赞数 2

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/marks59848660/article/details/51860465

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

今天要在MYSQL中创建一个视图，但是却失败了，提示View's SELECT contains a subquery in the FROM clause.

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

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW view_real AS SELECT c.scan_man AS scan_man, c.DOC_ID AS DOC_ID, c.db_time arrive_time ,d.db_time assign_time FROM (SELECT DOC_ID AS DOC_ID ,DB_TIME AS db_time, SCAN_MAN AS scan_man FROM test WHERE SCAN_TYPE=13) c LEFT JOIN (SELECT DOC_ID AS DOC_ID, DB_TIME AS db_time FROM test WHERE SCAN_TYPE=10) d ON c.DOC_ID = d.DOC_ID GROUP BY c.DOC_ID,c.scan_man;

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

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW view_real_1 AS SELECT DOC_ID AS DOC_ID ,DB_TIME AS db_time, SCAN_MAN AS scan_man FROM test WHERE SCAN_TYPE=13;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW view_real_2 AS SELECT DOC_ID AS DOC_ID, DB_TIME AS db_time FROM test WHERE SCAN_TYPE=10;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW view_real AS SELECT c.scan_man AS scan_man, c.DOC_ID AS DOC_ID, c.db_time arrive_time ,d.db_time assign_time FROM view_real_1 c LEFT JOIN view_real_2 d ON c.DOC_ID = d.doc_id GROUP BY c.doc_id,c.scan_man;