#一.where或having后面
/*
1.標量子查詢(當行子查詢)
2.列子查詢(多行子查詢)
3.行子查詢(多列多行)
特點:
①子查詢放在小括號內
②子查詢一般放在條件的右側
③標量子查詢,一般搭配著當行運算子使用
> < <= >= <>
④列子查詢,一般搭配著多行運算子使用
In and/some all
*/
#1.標量子查詢
#案例1:誰的工資比Abel 高?
#①查詢Abel的工資
SELECT salary
FROM employees
WHERE last_name='Abel'
#②查詢員工的資訊 滿足salary>①結果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
#案列2:回傳job_id與141號員工相同,salary比143號員工多的員工 姓名,job_id和工資
#①查詢141號員工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141;
#②查詢143號員工的salary
SELECT salary
FROM employees
WHERE employee_id=143;
#③查詢員工的姓名,job_id和工資,要求job_id=①并且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);
#案例3:回傳公司工資最少的員工的last_name,job_id和salary
#①查詢公司的 最低工資
SELECT MIN(salary)
FROM employees
#②last_name,job_id和salary 要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例4: 查詢最低工資大于50號部門最低工資的部門id和其最低工資
#①查詢50號部門的最低工資
SELECT MIN(salary)
FROM employees
WHERE department_id =50
#②查詢每個部門的最低工資
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
#③篩選②,滿足min(salary)> ①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id =50
);
#2.列子查詢（多行子查詢）★
#案例1：回傳location_id是1400或1700的部門中的所有員工姓名
#①查詢location_id是1400或1700的部門編號
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
#②查詢員工姓名，要求部門號是①串列中的某一個
SELECT last_name
FROM employees
WHERE department_id <>ALL(
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 DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
#②查詢員工號、姓名、job_id 以及salary，salary<(①)的任意一個
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#案例3：回傳其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工 的員工號、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#或
SELECT last_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';
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/288398.html
標籤：其他