牛客SQL练习第58题

题目描述

存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。
(你能不用select * from employees where emp_no >10005 这条语句完成吗，挑战一下自己对视图的理解)
输出格式:

emp_no	birth_date	first_name	last_name	gender	hire_date
10006	1953-04-20	Anneke	Preusig	F	1989-06-02
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15
10009	1952-04-19	Sumant	Peac	F	1985-02-18
10010	1963-06-01	Duangkaew	Piveteau	F	1989-08-24
10011	1953-11-07	Mary	Sluis	F	1990-01-22

解答：

emp_v的数据全部来自employees

select * from emp_v;

标题已经指明有这种解法:

select * from employees where emp_no >10005

select em.* from employees AS em, emp_v AS ev where em.emp_no = ev.emp_no

select * 
from employees e
where e.emp_no IN (select ev.emp_no from emp_v ev)

也可以用exists，正好复习下上题的exists

select * 
from employees 
where exists (select emp_no from emp_v where emp_no=employees.emp_no )