MYSQL创建视图from中包含子查询的问题

MYSQL创建视图from中包含子查询的问题

今天在学习Mysql创建视图遇到了一个很有趣的问题？

创建视图 emp_v2，要求查询部门的最高工资高于 12000 的部门信息

CREATE VIEW emp_v2
AS
	SELECT d.*
	FROM (SELECT MAX(salary) maxmoney, department_id
			FROM employees
			GROUP BY department_id) b , departments d
	WHERE b.department_id = d.`department_id` AND b.maxmoney > 12000;

使用这种方法却每创建成功，出现了以下错误：
查询：create view emp_v2 as select d.* from (SELECT MAX(salary) maxmoney, department_id FROM employees GROUP BY department_id) b , dep…错误代码： 1349
View’s SELECT contains a subquery in the FROM clause

大致的意思就是：视图的select在from 中包含了一个子查询。
后面自己想的方法是先子查询创建为视图，在根据此视图创建要求视图。

#把子查询创建为emp_v3的视图
CREATE VIEW emp_v3
AS
	SELECT MAX(salary) maxmoney, department_id
			FROM employees
			GROUP BY department_id;
#创建要求视图		
CREATE VIEW emp_v2
AS
	SELECT d.*
	FROM emp_v3 , departments d
	WHERE emp_v3.department_id = d.`department_id` AND  emp_v3.maxmoney > 12000;

总结：创建视图时 from后只能用 视图或表
但where后可以使用子查询。

CREATE VIEW myv5 AS
SELECT * FROM employees WHERE 
employee_id NOT IN(SELECT employee_id FROM employees WHERE department_id IS NULL);