mysql 列子查询

#案例：返回location_id是1400或1700的部门中的所有员工

SELECT  last_nameFROM employees WHERE department_id IN(    SELECT DISTINCT department_id    FROM departments    WHERE location_id IN (1400,1700));

#案例：返回其他工种中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id和salary

SELECT DISTINCT employee_id,last_name,job_id,salaryFROM employeesWHERE salary'IT_PROG' ;

或者

SELECT DISTINCT employee_id,last_name,job_id,salaryFROM employeesWHERE salary<(    SELECT DISTINCT MAX(salary)    FROM employees    WHERE job_id='IT_PROG') AND job_id <>'IT_PROG' ;

#案例：返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id和salary

SELECT DISTINCT employee_id,last_name,job_id,salaryFROM employeesWHERE salary'IT_PROG' ;

或者

SELECT DISTINCT employee_id,last_name,job_id,salaryFROM employeesWHERE salary<(    SELECT DISTINCT MIN(salary)    FROM employees    WHERE job_id='IT_PROG') AND job_id <>'IT_PROG' ;