SQL10:获取所有非manager的员工emp_no
获取所有非manager的员工emp_no
CREATE TABLE dept_manager
(dept_no
char(4) NOT NULL,emp_no
int(11) NOT NULL,from_date
date NOT NULL,to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
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
));
如插入为:
INSERT INTO dept_manager VALUES(‘d001’,10002,‘1996-08-03’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d002’,10006,‘1990-08-05’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d003’,10005,‘1989-09-12’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d004’,10004,‘1986-12-01’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d005’,10010,‘1996-11-24’,‘2000-06-26’);
INSERT INTO dept_manager VALUES(‘d006’,10010,‘2000-06-26’,‘9999-01-01’);
INSERT INTO employees VALUES(10001,‘1953-09-02’,‘Georgi’,‘Facello’,‘M’,‘1986-06-26’);
INSERT INTO employees VALUES(10002,‘1964-06-02’,‘Bezalel’,‘Simmel’,‘F’,‘1985-11-21’);
INSERT INTO employees VALUES(10003,‘1959-12-03’,‘Parto’,‘Bamford’,‘M’,‘1986-08-28’);
INSERT INTO employees VALUES(10004,‘1954-05-01’,‘Chirstian’,‘Koblick’,‘M’,‘1986-12-01’);
INSERT INTO employees VALUES(10005,‘1955-01-21’,‘Kyoichi’,‘Maliniak’,‘M’,‘1989-09-12’);
INSERT INTO employees VALUES(10006,‘1953-04-20’,‘Anneke’,‘Preusig’,‘F’,‘1989-06-02’);
INSERT INTO employees VALUES(10007,‘1957-05-23’,‘Tzvetan’,‘Zielinski’,‘F’,‘1989-02-10’);
INSERT INTO employees VALUES(10008,‘1958-02-19’,‘Saniya’,‘Kalloufi’,‘M’,‘1994-09-15’);
INSERT INTO employees VALUES(10009,‘1952-04-19’,‘Sumant’,‘Peac’,‘F’,‘1985-02-18’);
INSERT INTO employees VALUES(10010,‘1963-06-01’,‘Duangkaew’,‘Piveteau’,‘F’,‘1989-08-24’);
INSERT INTO employees VALUES(10011,‘1953-11-07’,‘Mary’,‘Sluis’,‘F’,‘1990-01-22’);
方法一:使用NOT IN选出在employees但不在dept_manager中的emp_no记录
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
方法二:先使用LEFT JOIN连接两张表,再从此表中选出dept_no值为NULL对应的emp_no记录
SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no)
WHERE dept_no IS NULL
方法三:方法二的简版,使用单层SELECT语句即可
SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL
上一篇: ELK实践(三)北京历年空气质量数据分析
下一篇: 因果图法设计测试用例