[大数据]MySQL——子查询案例分析

MySQL——子查询案例分析

1、查询工资大于149号员工工资的员工信息

#第一步 先查询149号的工资
SELECT employee_id,salary
FROM employees
WHERE employee_id = 149;
#第二步 在进行查询员工的信息
SELECT employee_id,salary
FROM employees
WHERE salary > (SELECT salary
				FROM employees
				WHERE employee_id = 149
               );

2、返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

#第一步 查询143号员工的工资
SELECT salary
FROM employees
WHERE employee_id = 143;
# 第二步查询信息
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;
SELECT salary
FROM employees
ORDER BY salary ASC
LIMIT 1;
# 第二步 进行查询last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT MIN(salary) 
                FROM employees
               );
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT salary
				FROM employees
				ORDER BY salary ASC
				LIMIT 1
               );

4、查询与141号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id

#第一步 查询141号员工的manager_id和department_id
SELECT manager_id
FROM employees
WHERE employee_id = 141
#######################
SELECT department_id
FROM employees
WHERE employee_id = 141

#第二步 查询其他员工的employee_id,manager_id,department_id
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (SELECT manager_id
                    FROM employees
                    WHERE employee_id = 141
                   )
AND department_id = (SELECT department_id
                     FROM employees
                     WHERE employee_id = 141
                    )
AND employee_id <> 141;

5、查询最低工资大于50号部门最低工资的部门id和其最低工资

# 第一步 查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;

# 第二步 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
				      FROM employees
					  WHERE department_id = 50
                      );

6、显示员工的employee_id,last_name和location,其中，若员工的department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’

# 第一步 查询location_id为1800员工的department_id
SELECT department_id
FROM departments
WHERE location_id = 1800;
# 第二步 
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;

7、查询平均工资最低的部门id

# 第一步 查询每个部门的最低工资按部门分组
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id;
# 第二步 查询最低平均工资
SELECT MIN(avg_sal)
FROM (SELECT AVG(salary) avg_sal
	  FROM employees
	  GROUP BY department_id) dept_avg_sal;
# 1、第三步 查询平均工资最低的部门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
					 );
# 2、第三步 查询平均工资最低的部门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
							);

8、查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
				SELECT AVG(salary)
				FROM employees e2
				WHERE department_id = e1.`department_id`
				);

SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
				SELECT department_id,AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id) tdas
WHERE e.department_id = tdas.department_id
AND e.salary > tdas.avg_sal;

9、查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary
FROM employees e
ORDER BY (
		SELECT department_name
		FROM departments d
		WHERE e.`department_id` = d.`department_id`);

10、若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些员工的employee_id和其job_id

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
    		SELECT COUNT(*)
			FROM job_history j
			WHERE e.`employee_id` = j.`employee_id`
			);

11、查询公司管理者的employee_id,last_name,job_id,department_id信息

第一种：自连接

SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.`manager_id` = mgr.`employee_id`;

第二种：子查询

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
					SELECT DISTINCT manager_id
					FROM employees);

第三种：使用EXISTS

SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
				SELECT *
				FROM employees e2
				WHERE e1.`employee_id` = e2.`manager_id`
			);

12、查询departments表中，不存在于employees表中的部门的department_id和department_name

第一种：多表连接

SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

第二种：使用EXISTS

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
					SELECT *
					FROM employees e
					WHERE d.`department_id` = e.`department_id`
					);

13、谁的工资比Abel高？

第一种：自连接

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