多行子查询练习

多行子查.png

#列子查询（多行子查询）


#返回location_id 是1400或1700的部门员工姓名


#①查询location_id 是1400或1700的部门编号
SELECT DISTINCT department_id FROM `departments`
WHERE location_id IN(1400,1700)


#② 
SELECT   e.first_name,  e.department_id
FROM employees e
WHERE e.department_id IN (SELECT DISTINCT department_id FROM `departments`
WHERE location_id IN (1400,1700))


#案例2：返回其他部门中比job_id 为‘IT_PROG’ 部门任一工资低的员工的工号、姓名、job_id 以及salary

#①   获取job_id 为 it_prog
SELECT salary 
FROM employees 
WHERE job_id  = 'IT_PROG'

#②  员工的工号、姓名、job_id 以及salary
SELECT  first_name, employee_id,job_id,salary
FROM employees 
WHERE salary >ANY(
SELECT salary 
FROM employees 
WHERE job_id  = 'IT_PROG'

) AND job_id <> 'IT_PROG'


#案例2：返回其他部门中比job_id 为‘IT_PROG’ 部门任一工资所有的员工的工号、姓名、job_id 以及salary

SELECT  first_name, employee_id,job_id,salary
FROM employees 
WHERE salary >ALL(
SELECT salary 
FROM employees 
WHERE job_id  = 'IT_PROG'

) AND job_id <> 'IT_PROG'

#或

SELECT  first_name, employee_id,job_id,salary
FROM employees 
WHERE salary >(
SELECT MIN(salary)  
FROM employees 
WHERE job_id  = 'IT_PROG'
) AND job_id <> 'IT_PROG'


# 行子查询（结果集一行多列或多行多列）
# 查询员工编号最小并且工资高的员工信息

SELECT  MIN(employee_id)
FROM employees

SELECT MAX(salary) FROM  employees

SELECT * FROM employees
WHERE employee_id = (
SELECT  MIN(employee_id)
FROM employees
) AND salary = (
SELECT MAX(salary) FROM  employees
)


#行子查询
SELECT * FROM employees
WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary)
FROM employees
)


/**
from 后面
将子查询结果充当一张表，要求必须起别名
*/

# 四 exists 后面 （相关子查询）
SELECT EXISTS(SELECT employee_id FROM employees
WHERE salary = 300000
)
/*
exists 完整的查询语句
结果
1 或 0
*/

#案例1：查询员工和部门名 
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT  *FROM employees  e
WHERE e.department_id= d.department_id
)

SELECT d.department_name
FROM departments d
WHERE d.department_id IN (
SELECT department_id 
FROM employees
)