十四、Oracle学习笔记：集合操作

一、集合
  多个查询语句，有可能取并集，交集，差集，
     union:取两个查询语句的并集，去重
     union all:去两个查询语句的并集，不去重
     minus:取两个查询语句的差集，第一个查询语句减去第二个查询语句中的共同部分
     intersect:取两个查询语句的公共部分(交集)

--练习：查询10号部门的员工信息 2.查询20号部门的员工信息 3.取并集，不去重
  select * from emp where deptno=10 union all select * from emp where deptno=20;
--练习：1.查询10号和20号的员工信息 
--     2.查20号和30号部门的信息 
--     3.取并集，去重 ；取交集；取差集 
--     4.
  select * from emp where deptno in(10,20) union select * from emp where deptno in(20,30); 
  select * from emp where deptno in(10,20) intersect select * from emp where deptno in(20,30);
  select * from emp where deptno in(10,20) minus select * from emp where deptno in(20,30);
     
--练习：1.查询10,20号部门的员工的员工编号，员工姓名，职位2.查询30号部门的员工的员工编号，员工姓名，工资
--      3.取并集，去重（注意，两条查询的列数必须相同，可以用null补位）
  select empno,ename,job,null,deptno from emp where deptno in (10,20)
  union 
  select empno,ename,null,sal,deptno from emp where deptno = 30 order by deptno;
      
--练习: 1.查询10，20号部门员工编号，员工姓名，职位，部门号
--     2. 查询30号部门号的员工编号，月薪，奖金，入职日期，部门号，3.取并集
  select empno,ename,job,null,null,null,deptno from emp where deptno in (10,20)
  union 
  select empno,null,null,sal,comm,hiredate,deptno from emp where deptno = 30 order by deptno;

           
二、集合排序操作
     order by 子句写在后一个查询语句中，要求排序的字段必须使用select子句中明确的字段名

     准备数据集

  drop table salestable;     
  create table salestable(
       year_id number not null,
       month_id number not null,
       day_id number not null,
       sales_value number(10,2) not null
  );

  insert into salestable 
  select 
  trunc(DBMS_RANDOM.value(2015,2018)) as year_id,   --2015~2017
  trunc(DBMS_RANDOM.value(1,13)) as month_id,
  trunc(DBMS_RANDOM.value(1,31)) as day_id,
  trunc(DBMS_RANDOM.value(1,100),2) as sales_value 
  from dual connect by level<=20;

  select * from salestable order by year_id,month_id,day_id;

 

--练习:1.按照年月日分组，统计每日的销售额
  select year_id,month_id,day_id,sum(sales_value) from salestable 
  group by year_id,month_id,day_id order by year_id,month_id,day_id;
--练习:2.统计每年的销售额
  select year_id,sum(sales_value) from salestable group by year_id order by year_id;
--练习:3.统计每月的销售额
  select year_id,month_id,sum(sales_value) from salestable group by year_id,month_id order by year_id,month_id;
--练习:4.统计12个月每个月的销售额
  select month_id,sum(sales_value) from salestable group by month_id order by month_id;
--练习::5.统计总销售额
  select sum(sales_value) from salestable; 
--练习::统计每年，每月，每日的销售额(使用集合)
  select null year_id,null month_id,day_id null,sum(sales_value) from salestable
  union
  select year_id,month_id,day_id,sum(sales_value) from salestable 
  group by year_id,month_id,day_id
  union
  select year_id,month_id,null day_id,sum(sales_value) from salestable 
  group by year_id,month_id 
  union
  select year_id,null month_id,null day_id,sum(sales_value) from salestable 
  group by year_id order by year_id,month_id,day_id; 

 

 

--练习：
--1:查询不与ford，allen同部门的员工信息(当null值在集合中时无效，原因是null!=null，null可以等于任何值)
  select * from emp where deptno not in(select deptno from emp where lower(ename) in ('ford','allen')) or is null;
--2:查询10,20各部门的平均工资，人数
  select deptno,avg(sal),count(*) from (select * from emp where deptno in (10,20)) group by deptno order by deptno;
--3:查询每个员工的姓名，职位，工资以及其直系领导的姓名与职位
  select e.ename,e.job,e.sal,f.ename,f.job from emp e,emp f where f.empno = e.mgr;
--4:使用exists查询没有员工的部门
  select * from dept where not exist (select 1 from emp e where e.deptno=d.deptno);
--5:按照员工编号排序，每页4条数据，查询第四页的数据
  select * from (select rownum rn,e.* from (select * from emp order by empno) e) where rn between 13 and 16;
--6：查询每年总销售额，与2017年每月的销售额
  select year_id,null,sum(sales_value) from salestable group by year_id 
  union 
  select null,month_id,sum(sales_value) from salestable where year_id=2017 group by year_id,month_id;
--7:查询三年中每月的销售额排名
  select salestable.*,row_number() over(partition by year_id,month_id order by sum(sales_value)) rn from(
  select year_id,month_id,sum(sales_value) from salestable group by year_id,month_id order by year_id,month_id);