oracle 开发 第07章 高级查询

 2016-01-27

目录

一、集合运算符
　　1.UNION ALL 操作符
　　2.UNION 操作符 (并集)
　　3.INTERSECT 操作符 (交集)
　　4.MINUS 操作符 (补集)
　　5.组合使用集合操作符
二、TRANSLATE()函数
三、DECODE()函数
四、CASE表达式
　　1.简单CASE表达式
　　2.搜索CASE表达式
五、层次化查询
　　1. START WITH 和CONNECT BY 子句
　　2.LEVEL伪列
　　3.获得树中层次总数
　　4.格式化层次化查询
　　5.从非根节点遍历查询(分支查询)
　　6.在START WITH 子句使用子查询(分支查询)
　　7.从下向上遍历树(单支查询)
　　8.删除节点
　　9.删除分支
　　10.其他条件查询
六、因子化层次查询
　　1. DFS(深度优先搜索)
　　2.BFS(广度优先搜索)
　　3.DFS查询经理以及为他们工作的员工人数
　　4.CYCLE子句
七、ROLLUP和CUBE子句
　　1.GROUP子句
　　2.ROLLUP子句
　　3.CUBE子句
　　4.GROUPING子句
　　5.GROUPING SETS子句
　　6.GROUPING_ID子句
　　7.GROUP_ID子句

 

 一、集合运算符

例题：表结构、表数据

insert all
    into products values (5,2,'Z Files','A description of modern science',19.95)
    into products values (6,2,'2412:The Return','Introduction to Chemistry',30)
    into products values (7,3,'Space Force 9','A star explodes',25.99)
    into products values (8,3,'From Another Planet','Action movie about a future war',13.95)
    into products values (9,4,'Classical Music','A description of modern science',19.95)
    into products values (10,4,'Pop 3','Introduction to Chemistry',30)
    into products values (11,4,'Creative Yell','A star explodes',25.99)
    into products values (12,,'My Front Line','Action movie about a future war',13.95)
select 1 from dual;

create table more_products (
prd_id integer constraint more_products_pk primary key,
prd_type_id integer constraint more_products_fk_product_types references product_types(product_type_id),
name varchar2(30) not null,
available char(1)
);

insert all 
into more_products values (1, 1, 'Modern Science', 'Y')
into more_products values (2, 1, 'Chemistry', 'N')
into more_products values (3, '', 'Supernova', 'N')
into more_products values (4, 2, 'Lunar Landing', 'Y')
into more_products values (5, 2, 'Submarine', 'Y')
select 1 from dual;

1.UNION ALL 操作符

--返回所有行，包括重复行
select product_id, product_type_id, name
  from products
union all
select prd_id, prd_type_id, name from more_products;

select product_id, product_type_id, name
  from products
union all
select prd_id, prd_type_id, name from more_products order by 1;

2.UNION 操作符 (并集)

--返回所有非重复行
select product_id, product_type_id, name
  from products
union /*all*/
select prd_id, prd_type_id, name from more_products order by 1;

3.INTERSECT 操作符 (交集)

--返回两个查询的共有行
select product_id, product_type_id, name
  from products
intersect
select prd_id, prd_type_id, name from more_products;

4.MINUS 操作符 (补集)

--返回从第一个查询检索出的行中，减去第二个查询检索出的行，之后剩余的行。
select product_id, product_type_id, name
  from products
minus
select prd_id, prd_type_id, name from more_products;

5.组合使用集合操作符

create table product_changes (
product_id integer constraint prod_changes_pk primary key,
product_type_id integer constraint prod_changes_fk_product_types references product_types(product_type_id),
name varchar2(30) not null,
description varchar2(50),
price number(5,2) );

insert all
    into product_changes values (1,1,'Modern Science','Introduction to Chemistry',30)
    into product_changes values (2,1,'New Chemistry','A description of modern science',19.95)
    into product_changes values (3,1,'Supernova','Introduction to Chemistry',30)
    into product_changes values (13,2,'Lunar Landing','A star explodes',25.99)
    into product_changes values (14,2,'Submarine','Action movie about a future war',13.95)
    into product_changes values (15,2,'Airplane','A description of modern science',19.95)
select 1 from dual;

select * from product_changes;

(A∪B)∩C=(A∩C)∪(B∩C)

(A∪B)∩C
(select product_id, product_type_id, name from products
union
select prd_id, prd_type_id, name from more_products)
intersect
select product_id, product_type_id, name from product_changes;

等价于
(A∩C)∪(B∩C)
(select product_id, product_type_id, name from products
intersect
select product_id, product_type_id, name from product_changes)
union
(select prd_id, prd_type_id, name from more_products
intersect
select product_id, product_type_id, name from product_changes);

A∪(B∩C)
select product_id, product_type_id, name from products
union
(select prd_id, prd_type_id, name from more_products
intersect
select product_id, product_type_id, name from product_changes);

二、TRANSLATE()函数

语法：translate(x,from_string,to_string)

select translate('secret message: meet me in the park',
                 'abcdefghijklmnopqrstuvwxyz',
                 'efghijklmnopqrstuvwxyzabcd')
  from dual;

select translate('wigvix qiwweki: qiix qi mr xli tevo',
                 'efghijklmnopqrstuvwxyzabcd',
                 'abcdefghijklmnopqrstuvwxyz')
  from dual;

select product_id,translate(name,
                 'abcdefghijklmnopqrstuvwxyz',
                 'efghijklmnopqrstuvwxyzabcd')
  from products;

三、DECODE()函数
语法：decode(col|expression,search1,result1[,search2,result2],...,default_value)

--判断1与1是否相等，相等则返回2，否则返回3
 select decode(1,1,2,3) from dual;
--判断1与2是否相等，相等则返回1，否则返回3
select decode(1,2,1,3) from dual;
--判断available字段与'Y'是否相等，相等则返回'Product is available'，否则返回'Product is not available'
select prd_id,
       available,
       decode(available,
              'Y',
              'Product is available',
              'Product is not available')
  from more_products;

四、CASE表达式
1.简单CASE表达式

语法：
case col|expr
when value1 then result1
when value2 then result2
...
when valueN then resultN
else default_result
end

select product_id,
       product_type_id,
       (case product_type_id
         when 1 then
          'Book'
         when 2 then
          'Video'
         when 3 then
          'DVD'
         when 4 then
          'CD'
         else
          'Magazine'
       end) "PRODUCT_TYPE_NAME"
  from products;

2.搜索CASE表达式

语法：
case
when expr1 then result1
when expr2 then result2
...
else default_result
end

select product_id,
       product_type_id,
       (case
         when product_type_id = 1 then
          'Book'
         when product_type_id = 2 then
          'Video'
         when product_type_id = 3 then
          'DVD'
         when product_type_id = 4 then
          'CD'
         else
          'Magazine'
       end) "PRODUCT_TYPE_NAME"
  from products;

select product_id,
       price,
       (case
         when price > 15 then
          'Expensive'
         else
          'Cheap'
       end) "PRICE_LEVEL"
  from products;

五、层次化查询

例题1：
一个表A中插入2016年的日期和星期几，这个怎么做？
比如表A中的数据应该为：
日期               星期
20160101          星期五
20160102          星期六
……
……

SELECT to_char(to_date(20170101, 'YYYYMMDD') - LEVEL, 'YYYYMMDD') "日期",
       to_char(to_date(20170101, 'YYYYMMDD') - LEVEL, 'day') "星期",
       LEVEL
  FROM dual
CONNECT BY LEVEL <= 366
ORDER BY "日期" ASC;

例题2：
create table more_employees (
employee_id integer constraint more_employees_pk primary key,
manager_id integer constraint more_emp1_fk_fk_more_emp1 references more_employees(employee_id),
first_name varchar2(10) not null,
last_name varchar2(10) not null,
title varchar2(20),
salary number(6,0) );

insert all
into more_employees values(1,'','James','Smith','CEO',800000)
into more_employees values(2,1,'Ron','Jonhson','Sales Manager',600000)
into more_employees values(3,2,'Fred','Hobbs','Sales Person',200000)
into more_employees values(4,1,'Susan','Jones','Support Manager',500000)
into more_employees values(5,2,'Rob','Green','Sales Person',40000)
into more_employees values(6,4,'Jane','Brown','Support Person',45000)
into more_employees values(7,4,'John','Grey','Support Manager',30000)
into more_employees values(8,7,'Jean','Blue','Support Person',29000)
into more_employees values(9,6,'Henry','Heyson','Support Person',30000)
into more_employees values(10,1,'Keivin','Black','Ops Manager',100000)
into more_employees values(11,10,'Keith','Long','Ops Person',50000)
into more_employees values(12,10,'Frank','Howard','Ops Person',45000)
into more_employees values(13,10,'Doreen','Penn','Ops Person',47000)
select 1 from dual;

--根节点：位于树顶端的节点
--父节点：下面有一个或多个节点
--子节点：之上有一个父节点
--页节点：没有子节点的节点
--兄弟节点：具有相同父节点的节点
语法
SELECT [LEVEL],column,expression,...
FROM table
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

--LEVEL是"伪列"，代表树的第几层
--START WITH子句定义层次化查询的起点
--CONNECT BY子句定义父行和子行的对应关系，employee_id=manager_id，表示父节点的employee_id和子节点的manager_id对应

1. START WITH 和CONNECT BY 子句

select employee_id, manager_id, first_name, last_name
  from more_employees
 start with employee_id = 1
connect by prior employee_id = manager_id;

2.LEVEL伪列

select level,employee_id,manager_id,first_name,last_name
from more_employees
start with employee_id = 1
connect by prior employee_id = manager_id
order by level;

3.获得树中层次总数

select count(distinct level)
from more_employees
start with employee_id = 1
connect by prior employee_id = manager_id;

4.格式化层次化查询

set pagesize 999
column employee format a25

select level,
       lpad(' ', 2 * level - 1) || first_name || ' ' || last_name as employee
  from more_employees
 start with employee_id = 1
connect by prior employee_id = manager_id
/* order by level*/;

5.从非根节点遍历查询(分支查询)

select level,
       lpad(' ', 2 * level - 1) || first_name || ' ' || last_name as employee
  from more_employees
 start with last_name = 'Jones'
connect by prior employee_id = manager_id;

select level,
       lpad(' ', 2 * level - 1) || first_name || ' ' || last_name as employee
  from more_employees
 start with employee_id = 4
connect by prior employee_id = manager_id;

6.在START WITH 子句使用子查询(分支查询)

select level,
       lpad(' ', 2 * level - 1) || first_name || ' ' || last_name as employee
  from more_employees
 start with employee_id = (select employee_id
                             from more_employees
                            where first_name = 'Keivin'
                              and last_name = 'Black')
connect by prior employee_id = manager_id;

7.从下向上遍历树(单支查询)

select level,
       lpad(' ', 2 * level - 1) || first_name || ' ' || last_name as employee
  from more_employees
 start with last_name = 'Blue'
connect by prior manager_id = employee_id;

8.删除节点

select level,
       lpad(' ', 2 * level - 1) || first_name || ' ' || last_name as employee
  from more_employees
 where last_name != 'Jonhson'
 start with employee_id = 1
connect by prior employee_id = manager_id;

9.删除分支

select level,
       lpad(' ', 2 * level - 1) || first_name || ' ' || last_name as employee
  from more_employees
 start with employee_id = 1
connect by prior employee_id = manager_id
       and last_name != 'Jonhson';

10.其他条件查询

select level,
       lpad(' ', 2 * level - 1) || first_name || ' ' || last_name as employee
  from more_employees
 where salary <= 50000
 start with employee_id = 1
connect by prior employee_id = manager_id;

六、因子化层次查询

with reporting_hierarchy(employee_id,
manager_id,
reporting_level,
first_name,
last_name) as
 (select employee_id, manager_id, 0 reporting_level, first_name, last_name
    from more_employees
   where employee_id = 1
  union all
  select e.employee_id,
         e.manager_id,
         reporting_level + 1,
         e.first_name,
         e.last_name
    from reporting_hierarchy r, more_employees e
   where r.employee_id = e.manager_id)
select employee_id, manager_id, reporting_level, first_name, last_name
  from reporting_hierarchy
 order by employee_id;

1. DFS(深度优先搜索)

with reporting_hierarchy(employee_id,
manager_id,
reporting_level,
first_name,
last_name) as
 (select employee_id, manager_id, 0 reporting_level, first_name, last_name
    from more_employees
   where manager_id is null
  union all
  select e.employee_id,
         e.manager_id,
         reporting_level + 1,
         e.first_name,
         e.last_name
    from reporting_hierarchy r, more_employees e
   where r.employee_id = e.manager_id) 
 search depth first by employee_id set order_by_employee_id
select employee_id,
       manager_id,
       reporting_level,
       lpad(' ', 2 * reporting_level) || first_name || ' ' || last_name as name
  from reporting_hierarchy
 order by order_by_employee_id;

2.BFS(广度优先搜索)

with reporting_hierarchy(employee_id,
manager_id,
reporting_level,
first_name,
last_name) as
 (select employee_id, manager_id, 0 reporting_level, first_name, last_name
    from more_employees
   where manager_id is null
  union all
  select e.employee_id,
         e.manager_id,
         reporting_level + 1,
         e.first_name,
         e.last_name
    from reporting_hierarchy r, more_employees e
   where r.employee_id = e.manager_id) 
   search breadth first by employee_id set order_by_employee_id
select employee_id,
       manager_id,
       reporting_level,
       lpad(' ', 2 * reporting_level) || first_name || ' ' || last_name as name
  from reporting_hierarchy
 order by order_by_employee_id;

3.DFS查询经理以及为他们工作的员工人数

with reporting_hierarchy(employee_id,
manager_id,
first_name,
last_name,
reporting_level,
employee_count) as
 (select employee_id,
         manager_id,
         first_name,
         last_name,
         0 reporting_level,
         0 employee_count
    from more_employees
  union all
  select e.employee_id,
         e.manager_id,
         e.first_name,
         e.last_name,
         reporting_level + 1,
         1 employee_count
    from reporting_hierarchy r, more_employees e
   where e.employee_id = r.manager_id) 
   search depth first by employee_id set order_by_employee_id
select employee_id,
       manager_id,
       first_name,
       last_name,
       sum(employee_count) as emp_count,
       max(reporting_level) as rept_level
  from reporting_hierarchy
 group by employee_id, manager_id, first_name, last_name
having max (reporting_level) > 0
 order by employee_id;

4.CYCLE子句

with reporting_hierarchy(employee_id,
manager_id,
reporting_level,
first_name,
last_name,
title) as
 (select employee_id,
         manager_id,
         0 reporting_level,
         first_name,
         last_name,
         title
    from more_employees
   where manager_id is null
  union all
  select e.employee_id,
         e.manager_id,
         reporting_level + 1,
         e.first_name,
         e.last_name,
         e.title
    from reporting_hierarchy r, more_employees e
   where r.employee_id = e.manager_id) 
   search depth first by employee_id set order_by_employee_id 
   cycle title set same_title to 'Y' default 'N'
select employee_id as emp_id,
       manager_id as mgr_id,
       lpad(' ', 2 * reporting_level) || first_name || ' ' || last_name as name,
       title,
       same_title
  from reporting_hierarchy
 order by order_by_employee_id;

七、ROLLUP和CUBE子句

例题表结构和表数据

create table divisions 
    (division_id char(3) constraint divisions_pk primary key,
        name varchar2(15) not null);
insert all
into divisions values ('SAL','Sales')
into divisions values ('OPE','Operations')
into divisions values ('SUP','Support')
into divisions values ('BUS','Business')
select 1 from dual;
select * from divisions;

create table jobs
    (job_id char(3) constraint jobs_pk primary key,
        name varchar2(20) not null);
insert all
    into jobs values ('WOR','Worker')
    into jobs values ('MGR','Manager')
    into jobs values ('ENG','Engineer')
    into jobs values ('TEC','Technologist')
    into jobs values ('PRE','President')
    select 1 from dual;
select * from jobs;

create table employee2 (employee_id integer constraint employee2_pk primary key,
division_id char(3) constraint employee2_fk_divisions references divisions(division_id),
job_id char(3) references jobs(job_id),
first_name varchar2(10) not null,
last_name varchar2(10) not null,
salary number(6,0));
insert all
    into employee2 values (1,'BUS','PRE','James','Smith',800000)
    into employee2 values (2,'SAL','MGR','Ron','Jonhson',350000)
    into employee2 values (3,'SAL','WOR','Fred','Hobbs',140000)
    into employee2 values (4,'SUP','MGR','Susan','Jones',200000)
    into employee2 values (5,'SAL','WOR','Rob','Green',350000)
    select 1 from dual;
select * from employee2 where rownum <= 5;

1.GROUP子句

--对division_id字段进行分类汇总
select division_id, sum(salary)
  from employee2
 group by division_id
 order by division_id;

2.ROLLUP子句

--对GROUP汇总的结果进行再汇总
--1.向ROLLUP传递一列
select division_id, sum(salary)
  from employee2
 group by rollup(division_id)
 order by division_id;
--2.向ROLLUP传递多列
select division_id, job_id, sum(salary)
  from employee2
 group by rollup(division_id, job_id)
 order by division_id, job_id;

select job_id, division_id, sum(salary)
  from employee2
 group by rollup(job_id, division_id)
 order by job_id, division_id;

3.CUBE子句

--对ROLLUP汇总的结果进行再汇总
select division_id, job_id, sum(salary)
  from employee2
 group by cube(division_id, job_id)
 order by division_id, job_id;

select job_id, division_id, sum(salary)
  from employee2
 group by cube(job_id, division_id)
 order by job_id, division_id;

4.GROUPING子句

--对ROLLUP、CUBE汇总的列加上说明
select division_id, sum(salary)
  from employee2
 group by rollup(division_id)
 order by division_id;
--1.对ROLLUP使用GROUPING
select grouping(division_id), division_id, sum(salary)
  from employee2
 group by rollup(division_id)
 order by division_id;
--2.使用CASE、GROUPING转换单列
select case grouping(division_id)
         when 1 then
          'All division'
         else
          division_id
       end as div,
       sum(salary)
  from employee2
 group by rollup(division_id)
 order by division_id;
--3.使用CASE、GROUPING转换多列
select case grouping(division_id)
         when 1 then
          'All division'
         else
          division_id
       end as div,
       case grouping(job_id)
         when 1 then
          'All jobs'
         else
          job_id
       end as job,
       sum(salary)
  from employee2
 group by rollup(division_id, job_id)
 order by division_id, job_id;
--4.使用CUBE、GROUPING转换多列
select case grouping(division_id)
         when 1 then
          'All division'
         else
          division_id
       end as div,
       case grouping(job_id)
         when 1 then
          'All jobs'
         else
          job_id
       end as job,
       sum(salary)
  from employee2
 group by cube(division_id, job_id)
 order by division_id, job_id;

5.GROUPING SETS子句

select division_id, job_id, sum(salary)
  from employee2
 group by grouping sets(division_id, job_id)
 order by division_id, job_id;

6.GROUPING_ID子句

select division_id,
       job_id,
       grouping(division_id) as div_grp,
       grouping(job_id) as job_jrp,
       grouping_id(division_id, job_id) as grp_id,
       sum(salary)
  from employee2
 group by cube(division_id, job_id)
 order by division_id, job_id;

select division_id,
       job_id,
       grouping_id(division_id, job_id) as grp_id,
       sum(salary)
  from employee2
 group by cube(division_id, job_id)
having grouping_id(division_id, job_id) > 0
 order by division_id, job_id;

select division_id, job_id, sum(salary)
  from employee2
 group by division_id, rollup(division_id, job_id);

7.GROUP_ID子句

select division_id, job_id, group_id(), sum(salary)
  from employee2
 group by division_id, rollup(division_id, job_id);

select division_id, job_id, group_id(), sum(salary)
  from employee2
 group by division_id, rollup(division_id, job_id)
having group_id() = 0;

 

【参考资料】

[1] Jason Price.精通Oracle Database 12c SQL&PLSQL编程(第3版).[M].北京：清华大学出版社，2014