河南住房和建设厅网站,怎样在wordpress设置伪静态,长宁区网站建设设计,做ic哪些网站好做一、set运算符 union#xff1a;得到两个查询结果的并集#xff0c;并且⾃动去掉重复⾏。不会排序 union all#xff1a;得到两个查询结果的并集#xff0c;不会去掉重复⾏。也不会排序 intersect#xff1a;得到两个查询结果的交集#xff0c;并且按照结果集的第⼀个列进…一、set运算符 union得到两个查询结果的并集并且⾃动去掉重复⾏。不会排序 union all得到两个查询结果的并集不会去掉重复⾏。也不会排序 intersect得到两个查询结果的交集并且按照结果集的第⼀个列进⾏排序 minus得到两个查询结果的减集以第⼀列进⾏排序
--查询部门的部门号其中不包括job_id是”ST_CLERK”的部门号
select department_id
from departments
minus
select department_id
from employees
where job_id ST_CLERK--查询105020号部门的job_iddepartment_id
--并且department_id按105020的顺序排列
column a_dummy noprint --隐藏后面的1、2、3序列号select job_id,department_id,1 a_dummy
from employees
where department_id 10
union
select job_id,department_id,2
from employees
where department_id 50
union
select job_id,department_id,3
from employees
where department_id 20
order by 3 --使用order by对结果集排序--查询所有员工的last_name ,department_id 和department_name
select last_name,department_id,to_char(null) department_name
from employees
union
select to_char(null),department_id,department_name
from departments二、高级子查询
1.多列子查询
多列子查询中的比较分为两种成对比较、不成对比较
成对比较
--查询与141号或174号员工的manager_id和department_id相同的
--其他员工的employee_id, manager_id, department_id
select employee_id,manager_id,department_id
from employees
where (manager_id,department_id) in (select manager_id,department_idfrom employeeswhere employee_id in (141,174))
and employee_id not in (141,174)不成对比较
select employee_id,manager_id,department_id
from employees
where manager_id in (select manager_idfrom employeeswhere employee_id in (141,174))
and department_id in (select department_idfrom employeeswhere employee_id in (141,174))
and employee_id not in (141,174)2.在 FROM 子句中使用子查询
案例对比
--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
select last_name,department_id,salary,
(select avg(salary) from employees e3 where e1.department_id e3.department_id group by department_id) avg_sal
from employees e1
where salary (select avg(salary)from employees e2where e1.department_id e2.department_idgroup by department_id)--在 FROM 子句中使用子查询
--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
select last_name,e1.department_id,salary,e2.avg_sal
from employees e1,(select department_id,avg(salary) avg_sal from employees group by department_id) e2
where e1.department_id e2.department_id
and e1.salary e2.avg_sal由上可知在 FROM 子句中使用子查询可以减少冗余
3.单列子查询
1在 CASE 表达式中使用单列子查询
--显示员工的employee_id,last_name和location。
--其中若员工department_id与location_id为1800的department_id相同则location为’Canada’
--其余则为’USA’。
select employee_id,last_name,
(case department_id when (select department_id from departments where location_id 1800) then Canadaelse USA end) location
from employees2在 ORDER BY 子句中使用单列子查询
--查询员工的employee_id,last_name,要求按照员工的department_name排序
select employee_id,last_name
from employees e
order by (select department_namefrom departments dwhere e.department_id d.department_id) asc4.相关子查询
子查询中使用主查询中的列主查询的每一行都执行一次子查询
--查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
select last_name,salary,department_id
from employees e1
where salary (select avg(salary)from employees e2where e1.department_id e2.department_id)--若employees表中employee_id与job_history表中employee_id相同的数目不小于2
--输出这些相同id的员工的employee_id,last_name和其job_id
select employee_id,last_name,job_id
from employees e
where 2 (select count(1)from job_historywhere e.employee_id employee_id )5.EXISTS操作符和NOT EXISTS操作符
EXISTS 操作符检查在子查询中是否存在满足条件的行 如果满足条件则输出 NOT EXISTS操作符正好相反
--查询公司管理者的employee_id,last_name,job_id,department_id信息
select employee_id,last_name,job_id,department_id
from employees mgr
where exists(select Afrom employees empwhere mgr.employee_id emp.manager_id)--查询departments表中不存在于employees表中的部门的department_id和department_name
select department_id,department_name
from departments d
where not exists(select Afrom employeeswhere department_id d.department_id)6.WITH 子句
使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块 WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中 使用 WITH 子句可以提高查询效率
--查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
with dept_sumsal as(
select department_name,sum(salary) sum_sal
from employees e,departments d
where e.department_id d.department_id
group by department_name
),
dept_avgsal as(
select sum(sum_sal)/count(1) avg_sum_sal
from dept_sumsal
)select *
from dept_sumsal
where sum_sal (select avg_sum_salfrom dept_avgsal)
order by department_name
