oracle子查询

最新推荐文章于 2022-08-17 11:11:46 发布

2013学习的人

最新推荐文章于 2022-08-17 11:11:46 发布

阅读量533

点赞数

分类专栏： Oracle 文章标签： oracle plsql

本文链接：https://blog.csdn.net/jiangyw2014/article/details/24932031

版权

Oracle 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.单行子查询(单行比较操作符：=,>=,<=,<,<>,>)

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

2.子查询中使用组函数

SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = 
                (SELECT MIN(salary)
                 FROM   employees);

3.子查询中使用Having

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

4.非法使用子查询

说明：group by返回的是多行，而=号是返回单行

SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

5.多行子查询(多行比较操作符：in,any,all)

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ANY
                    (SELECT 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 < ALL
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

6.子查询中的空值问题

SELECT emp.last_name
FROM   employees emp
WHERE  emp.employee_id NOT IN
                             (SELECT mgr.manager_id
                              FROM   employees mgr);