03-子查询

子查询:单行/多行

查询时基于未知的值时,应使用子查询。

过滤条件有未知的用子查询。

子查询在主查询之前一次执行完成。

子查询的结果被主查询使用。

子查询在括号内,放在比较条件右侧。

单行子查询比较符号(<、>、=、<=、>=、<>)。

多行子查询比较符号(in、any(任一)、all(全部))。

 

eg:

 1 --子查询中的 HAVING 子句
 2 --首先执行子查询。
 3 --向主查询中的HAVING 子句返回结果。
 4 --查询最低工资大于50号部门最低工资的部门id和其最低工资
 5 SELECT   department_id, MIN(salary)
 6 FROM     employees
 7 GROUP BY department_id
 8 HAVING   MIN(salary) >
 9                        (SELECT MIN(salary)
10                         FROM   employees
11                         WHERE  department_id = 50);
12 
13 --执行单行子查询
14 --返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id 和工资
15 SELECT last_name, job_id, salary
16 FROM   employees
17 WHERE  job_id =  
18                 (SELECT job_id
19                  FROM   employees
20                  WHERE  employee_id = 141)
21 AND    salary >
22                 (SELECT salary
23                  FROM   employees
24                  WHERE  employee_id = 143);
25 
26 --in:等于列表中的任意一个
27 --any :和子查询返回的某一个值比较
28 --返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
29 SELECT employee_id, last_name, job_id, salary
30 FROM   employees
31 WHERE  salary < any
32                     (SELECT salary
33                      FROM   employees
34                      WHERE  job_id = 'IT_PROG')
35 AND    job_id <> 'IT_PROG';
36 
37 --all:和子查询返回的所有值比较
38 --返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
39 SELECT employee_id, last_name, job_id, salary
40 FROM   employees
41 WHERE  salary < ALL
42                     (SELECT salary
43                      FROM   employees
44                      WHERE  job_id = 'IT_PROG')
45 AND    job_id <> 'IT_PROG';

 

eg:

 1 --查询和Zlotkey相同部门的员工姓名和雇用日期
 2 select last_name,hire_date
 3 from employees
 4 where department_id = (
 5                           select department_id
 6                         from employees
 7                           where last_name = 'Zlotkey'
 8                           )
 9 and last_name <> 'Zlotkey';
10 --查询工资比公司平均工资高的员工的员工号,姓名和工资。
11 select last_name,employee_id,salary
12 from employees
13 where salary > (select avg(salary)
14                    from employees);
15 --查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
16 select employee_id,last_name,salary
17 from employees e1
18 where salary > (
19                    select avg(salary)
20                    from employees e2
21                    where e1.department_id = e2.department_id
22                    group by department_id
23                    );
24 --查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
25 select employee_id,last_name
26 from employees
27 where department_id in (
28                            select department_id
29                            from employees
30                            where last_name like '%u%'
31                             )
32     and last_name not like '%u%';
33 --查询在部门的location_id为1700的部门工作的员工的员工号
34 select employee_id
35 from employees
36 where department_id in (
37                        select department_id
38                        from departments
39                        where location_id = 1700
40                        );
41 --查询管理者是King的员工姓名和工资
42 select last_name,salary
43 from employees
44 where manager_id in (
45                    select employee_id
46                    from employees
47                    where last_name = 'King'
48                    );

 

posted @ 2020-07-29 16:56  路修索  阅读(142)  评论(0编辑  收藏  举报