MySQL - 基础题练习题 - 视图(12)

数据在本专栏的第一篇博客里

13.1.创建视图emp_v1，要求查询电话号码以"011"开头的员工姓名和工资、邮箱

CREATE 
	OR REPLACE VIEW emp_v1 AS SELECT
	last_name,
	salary,
	email 
FROM
	employees 
WHERE
	phone_number LIKE '011%';

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

#1. 视图方法：
CREATE 
	OR REPLACE VIEW emp_v2 AS SELECT
	max( salary ) mx_sal,
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	max( salary )> 12000;

#
SELECT
	d.*,
	m.mx_sal 
FROM
	departments d
	JOIN emp_v2 m ON m.department_id = d.department_id;

我觉得不能只看代码量，上面的视图方法可读性会更好一些

#2. 子查询方法：
SELECT
	d.*,
	m.mx_sal 
FROM
	departments d
	JOIN ( SELECT max( salary ) mx_sal, department_id FROM employees GROUP BY department_id HAVING max( salary )> 12000 ) m ON m.department_id = d.department_id;