SQL 基础之使用子查询检索数据（二十二）
多列子查询where (manager_id, department_id) in
子查询
100 90
102 60
124 50
主查询的每行都与多行和多列的子查询进行比较
列的比较
多列的比较，包含子查询可以是：
不成对比较
成对比较
成对比较子查询
1、显示与员工名为“John”同部门且同一个经理的其它员工信息
select employee_id, manager_id, department_id from empl_demo
where (manager_id, department_id) IN
(select manager_id, department_id from empl_demo
where first_name = 'John')
AND first_name'John';
不成对比较
1、显示名字不为 “John”员工的经理ID和部门ID的员工号、经理号、部门号
select employee_id, manager_id, department_id
from empl_demo
where manager_id in
(select manager_id
from empl_demo
where first_name = 'john')
and department_id in
(select department_id
from empl_demo
where first_name = 'john')
and first_name'john';
标量子查询表达式
标量子查询是从一行中返回一列的子查询
标量子查询可在下列情况下使用:
– DECODE 和 CASE 条件和表达式的一部分
– SELECT 中除 GROUP BY 子句以外的所有子句中
– UPDATE 语句的 SET 子句和 WHERE 子句
CASE 表达式中的标量子查询：
select employee_id, last_name, department_id,
(case
when department_id =
(select department_id
from departments
where location_id = 1800)
then 'canada' else 'usa' end) location
from employees;
ORDER BY 子句中的标量子查询：
select employee_id, last_name,department_id
from employees e
order by (select department_name
from departments d
where e.department_id = d.department_id);
相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
子查询中使用主查询中的列
select column1, column2, ...
from table1 Outer_table
where column1 operator
(selecT column1, column2
from table2
where expr1 = Outer_table.expr2);
2、查找所有的员工信息，谁的薪金超过其部门的平均工资
select last_name, salary, department_id
from employees outer_table
where salary >
(selecT AVG(salary)
from employees inner_table
where inner_table.department_id =
outer_table.department_id);
3、显示哪些员工工作变更过至少两次
select e.employee_id, last_name,e.job_id from employees e
where 2
(select dept_avg
from avg_cost)
order by department_name;
递归 WITH 子句
递归WITH子句：
Enables formulation of recursive queries.
Creates query with a name, called the Recursive WITH element name
Contains two types of query blocks member: anchor and a recursive
Is ANSI-compatible
with reachable_from (source, destin, totalflighttime) as
(
select source, destin, flight_time
from flights
union all
select incoming.source, outgoing.destin,
incoming.totalflighttime+outgoing.flight_time
from reachable_from incoming, flights outgoing
where incoming.destin = outgoing.source
)
select source, destin, totalflighttime
from reachable_from;
页:
[1]