MySQL子查询作为列_mysql 列子查询

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

SELECT last_name

FROM 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,salary

FROM employees

WHERE salary

SELECT DISTINCT salary

FROM employees

WHERE job_id='IT_PROG'

) AND job_id <>'IT_PROG' ;

或者

SELECT DISTINCT employee_id,last_name,job_id,salary

FROM employees

WHERE 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,salary

FROM employees

WHERE salary

SELECT DISTINCT salary

FROM employees

WHERE job_id='IT_PROG'

) AND job_id <>'IT_PROG' ;

或者

SELECT DISTINCT employee_id,last_name,job_id,salary

FROM employees

WHERE salary

SELECT DISTINCT MIN(salary)

FROM employees

WHERE job_id='IT_PROG'

) AND job_id <>'IT_PROG' ;