第九章 子查询

#查询员工中工资比Abel多的人
#子查询
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name=‘Abel’ AND e1.salarysalary;

#子查询
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name=‘Abel’
);

#查询可分为单行子查询和多行子查询，也可以分为相关子查询与不相关子查询

#相关子查询：查询工资大于本部门平均工资的员工信息
SELECT last_name,department_id,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
)
GROUP BY department_id;

#不相关子查询：查询工资大于本公司平均工资的员工信息
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
)

#查询工资大于149号员工工资的员工的信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE employee_id=149
);

#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
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
);

#返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);

#查询与141号(employee_id)员工的manager_id和department_id相同的其他员工的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;

#方式二：成对比较
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id)=(
SELECT manager_id,department_id
FROM employees
WHERE employee_id=141
)
AND employee_id <>141;

#查询与141号或174员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
#方式一：不成对比较
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN(141,174))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN(141,174))
AND employee_id NOT IN(141,174);

#成对比较
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);

#having中的子查询
#查询最低工资大于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
);

#case中的子查询
#显示员工的employee_id,last_name和location,其中,若员工department_id与location_id为1800的department_id相同，则lacation为’Canada’,其余为’USA’
SELECT employee_id,last_name,
(CASE department_id
WHEN
(SELECT department_id FROM departmentsemployees
WHERE location_id=1800)
THEN ‘Canada’ ELSE ‘USA’ END)
AS location
FROM employees;

#查询书名和类型，其中note值为novel显示小说，law显示法律，
medicine显示医药，cartoon显示卡通，joke显示笑话
SELECT name’书名’,note,
CASE note
WHEN’novel’ THEN’小说’
WHEN’law’THEN’法律’
WHEN’medicine’THEN’医药’
WHEN’cartoon’THEN’卡通’
WHEN’joke’THEN’笑话’
ELSE’其他’
END
AS ‘类型’
FROM books;

查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，

#显示畅销，为0的显示需要无货
SELECT nameAS’书名’,num AS’库存’,
CASE WHEN num>30 THEN’滞销’
WHEN 0 WHEN num=0 THEN’无货’
ELSE ‘正常’
END
AS ‘显示状态’
FROM books;

#子查询的空值情况:因为里面的子查询为空，没有叫Haas的人，所有最终没有返回任何行
SELECT last_name,job_id
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE last_name=‘Haas’
)

#非法使用子查询:用等于会报错，因为这个子查询会返回多行数据，把等号改为in即可
SELECT employee_id,last_name
FROM employees
WHERE salary =(
SELECT MIN(salary)
FROM employees
GROUP BY department_id
)

#因为有不同的员工领着相同的最低工资
SELECT employee_id,last_name
FROM employees
WHERE salary IN(
SELECT MIN(salary)
FROM employees
GROUP BY department_id
)

#多行子查询
#多行子查询的操作符：in() any all some(是any的别名，一般用any)

#返回其他job_id中比job_id为’IT_PROG’部门任一员工工资低的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary SELECT salary
FROM employees
WHERE job_id=‘IT_PROG’)
AND job_id!=‘IT_PROG’

SELECT* FROM employees
WHERE employee_id!=101;

#返回其他job_id中比job_id为’IT_PROG’部门工资都低的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN(salary)
FROM employees
WHERE job_id=‘IT_PROG’)
AND job_id!=‘IT_PROG’

#或者
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary SELECT salary
FROM employees
WHERE job_id=‘IT_PROG’)
AND job_id!=‘IT_PROG’

#查询平均工资最低的部门id
#方式一:还是方式一用any,all这种容易理解
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)

#方式二:三个字段别名一定要有，否则报错,every derived table must have its own itias
#每一个保留下的表都要有自己的别名
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
)

#看下面第一个错，第二个正确 :嵌套函数要以其命名别名的方式
SELECT MIN(AVG(salary))
FROM (
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)

SELECT MIN(ll)
FROM (
SELECT AVG(salary)ll
FROM employees
GROUP BY department_id
)kkk

#相关子查询:比较重要，每次都内查询一次
#回顾：查询员工中工资大于本部门平均工资的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
)

#查询员工中工资大于60号部门平均工资的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
WHERE department_id=60
)

#相关子查询：查询员工中工资大于60号部门平均工资的last_name,salary和其department_id 查询员工中工资大于本部门平均工资的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary>(
SELECT AVG(salary)
FROM employees
WHERE department_id=e1.department_id
)

#在from中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,(
SELECT department_id,AVG(salary)dept_avg_sal #作为表的字段出现的而不是函数，必须起别名
FROM employees
GROUP BY department_id)e2
WHERE e1.department_id=e2.department_id
AND e2.dept_avg_sal

#查询员工的id,salary,按照department_name排序
SELECT e1.department_id,employee_id,salary
FROM employees e1
ORDER BY(
SELECT department_name
FROM departments e2
WHERE e1.department_id=e2.department_id
)ASC;

#查询员工的id,salary,按照department_name进行排序
#如果查询的字段的表之中都有并且两表之间join,应指定表的字段
SELECT employee_id,salary
FROM employees e1
ORDER BY(
SELECT department_id
FROM departments e2
WHERE e1.department_id=e2.department_id
);

#若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同的id的员工employee_id,last_name,job_id

#下面这个是错误的
SELECT employees.employee_id,last_name,employees.job_id
FROM employees JOIN job_history
ON employees.employee_id=job_history.department_id;

#正确的如下，count(*)可写为count(字段名)
SELECT e.employee_id,last_name,e.job_id
FROM employees e
WHERE 2<=(
#select count() //可以count()或者count(字段)
SELECT COUNT(employee_id)
FROM job_history
WHERE employee_id=e.employee_id
);

#exists与 not exists关键字
#以下两个题是用的自连接
#查询公司员工的的employee_id,last_name,job_id,department_id信息
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id NOT IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);

#查询公司管理者的employee_id,last_name,job_id,department_id信息:自连接,要distinct对管理者去重
SELECT DISTINCT e2.employee_id,e2.last_name,e2.job_id,e2.department_id
FROM employees e1
JOIN employees e2
ON e1.manager_id=e2.employee_id；

#查询公司管理者的employee_id,last_name,job_id,department_id信息：用的子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
);

#查询公司管理者的employee_id,last_name,job_id,department_id信息：用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
)

#查询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;

#方式二
SELECT department_id,department_name
FROM departments d
WHERE EXISTS(
SELECT* FROM employees e
WHERE d.department_id=e.department_id
);