Oracle 19C学习 - 08. 使用子查询解决查询问题

2023-08-06 21:39:10

（本博文采用的数据库是Oracle自带的hr用户范本数据库)

子查询可以将查询到的结果作为主查询的一部分。

例如下面的范例，需要先找出来Davies的入职日期，然后代入到主查询。

SELECT last_name
FROM employees
WHERE hire_date > (SELECT hire_date FROM employees WHERE last_name = 'Davies');

使用子查询的规则和准则

将子查询括在括号中。
将子查询放在比较条件的右侧，以提高可读性。（但是，子查询可以出现在比较运算符的任何一侧。）
将单行运算符用于单行子查询，将多行运算符用于多行子查询。

单行子查询

单行子查询只返回一行记录。

与单行子查询一起工作的运算符。

查找与Austin职位相同，但比他工资高的人员。

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
                SELECT job_id
                FROM employees
                WHERE last_name = 'Austin')
AND
      salary > (
                SELECT salary 
                FROM employees
                WHERE last_name = 'Austin');

在子查询中使用聚合函数

找到工资最低的人员信息。

SELECT first_name || '.' || last_name, salary
FROM employees
WHERE salary = (
                SELECT MIN(salary)
                FROM employees);

找出来最低工资高于10号部门的最高工资的部门。

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING Min(salary) > (
                SELECT MIN(salary)
                FROM employees
                WHERE department_id = 10);

多行子查询

返回结果是多行的子查询

ANY运算符实例：

找出所有比IT_PROG这个职位任何一个人工资低，而且不是IT_PROG的人员。IT_PROG人员的工资范围是4200~9000，也就是如果工资比9000低就能满足条件。

SELECT last_name, salary, job_title
FROM employees e JOIN jobs j
ON e.job_id = j.job_id
WHERE salary < ANY (
                    SELECT salary 
                    FROM employees
                    WHERE job_id = 'IT_PROG'
                    );
--搜索的结果是工资从2100 - 8800的人员

ALL运算符实例：

找出所有比IT_PROG这个职位所有人的工资都要低，而且不是IT_PROG的人员。IT_PROG人员的工资范围是4200~9000，也就是如果工资比4200低就能满足条件。

SELECT last_name, salary, job_title
FROM employees e JOIN jobs j
ON e.job_id = j.job_id
WHERE salary < ALL (
                    SELECT salary 
                    FROM employees
                    WHERE job_id='IT_PROG'
                    );
--搜索的结果是工资从2100 - 4100的人员

IN运算符实例：

找出每个部门工资最高的员工。当记录的(department_id, salary)的值与子查询的内容相符的时候，这条记录就作为有效值。

SELECT last_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN (
                                  SELECT department_id, MAX(salary)
                                  FROM employees
                                  GROUP BY department_id);

在这个例子中，如果改为下面的写法，那么任何一条记录的salary与子查询相符的记录都作为有效值，失去了限定部门这一维度。

SELECT last_name, department_id, salary
FROM employees
WHERE salary IN (
                 SELECT MAX(salary)
                 FROM employees
                 GROUP BY department_id
　　　　　　　　　　);  
--例如90部门的最高工资17000， 而张三是50部门的，他的工资是17000，那么他也会出现在主查询的报表中。

找出是经理职位的员工(员工ID出现在其他人的manager_id)。

SELECT last_name, salary
FROM employees emp
WHERE emp.employee_id IN (
                    SELECT mgr.manager_id
                    FROM employees mgr
                    WHERE NOT(manager_id is null));

内联视图子查询

子查询得到的视图作为主查询的数据源。

SELECT * 
FROM (
        SELECT *
        FROM employees
        WHERE department_id = 50
      );

如果想拿到内联视图子查询结果的聚合查询字段，需要将该字段设置别名。如果从内联视图子查询结果获取真实字段，可以直接引用，无需列别名

SELECT TotalSalary   
FROM (SELECT sum(salary) as TotalSalary FROM employees)

将聚合函数作为子查询，成为单独的字段。

将求出来的薪资最小值，作为单独的一行，与每个员工的薪资进行对比。

SELECT last_name, salary, (SELECT MIN(salary) FROM employees)
FROM employees;

码农公寓

相关文章