1_MySQL子查询

Justinyh

已于 2023-01-17 18:00:51 修改

阅读量291

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-10-18 20:54:01 首次发布

本文链接：https://blog.csdn.net/qq_38926742/article/details/127395978

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

MySQL文章目录

文章目录

MySQL文章目录
一、子查询
- 1.1、谁的工资比李白高？
二、单行子查询
- 2.1.成对比较
三、多行子查询

一、子查询

1.1、谁的工资比李白高？

方式一：多次查询

SELECT salary
FROM employees
WHERE last_name = '李白';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;

方式二：自连接

SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = '李白'
AND e1.`salary` < e2.`salary`

方式三：子查询

SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = '李白'
);

二、单行子查询

2.1.成对比较

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
		(SELECT manager_id, department_id
		FROM employees
		WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);

三、多行子查询

题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的工号、姓名、job_id以及salary

在这里插入图片描述

题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

在这里插入图片描述

题目：查询平均工资最低的部门id

#方式1：
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
		)

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
	SELECT AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id
	)