mysql - mysql 中的嵌套 View 问题

Question

这个问题已经有答案了:

View's SELECT contains a subquery in the FROM clause (4 个回答)

已关闭 6 年前。

CREATE or REPLACE VIEW myView AS
SELECT t2.film_id, t2.no_actors AS n_actor, COALESCE(t1.no_copies,0) AS n_inventory FROM
(SELECT film_id, count(film_id) AS no_copies FROM inventory GROUP BY film_id) AS t1 RIGHT OUTER JOIN 
(SELECT film_id, count(actor_id) 
AS no_actors FROM film_actor GROUP BY film_id) AS t2 
ON t2.film_id=t1.film_id;

上面的查询给出了错误:

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

我们如何在一个查询中为上述查询编写嵌套 View ？请帮忙。提前致谢。

Answer 1

您不能在创建 View 中使用子查询，但可以通过这种方式使用其他 View

CREATE or REPLACE VIEW myViewSub1 AS 
SELECT film_id, count(film_id) AS no_copies FROM inventory GROUP BY film_id
;


CREATE or REPLACE VIEW myViewSub2 AS 
SELECT film_id, count(actor_id) 
AS no_actors FROM film_actor GROUP BY film_id
;

CREATE or REPLACE VIEW myView AS
SELECT t2.film_id, t2.no_actors AS n_actor, COALESCE(t1.no_copies,0) AS n_inventory 
FROM  myViewSub1 AS t1 
RIGHT OUTER JOIN myViewSub2 AS t2 
ON t2.film_id=t1.film_id
;