多行子查.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
)