MySQL子查询的使用详解上篇

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name , salary
FROM employees
WHERE salary > 1 1 000 ;
#方式二：自连接
SELECT e2 .last_name , e2 . salary
FROM employees e1 , employees e2
WHERE e1.last_name = ' Abel '
AND e1. ` salary ` < e2. ` salary `

#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

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);

SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);

SELECT employee_id, last_name,
(CASE department_id
WHEN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;

SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
        SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id）

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_sal)
FROM (
        SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
        ) dept_avg_sal
)