第七章 高级查询

第七章 高级查询
7.1使用集合操作符
集合操作符可以将两个或多个查询返回的行组合起来。
UNION ALL 返回各个查询检索出的所有行，包括重复的行
可以使用ORDER BY子句根据查询中列的位置对行进行排序，根据第一列进行排序：
Order by 1 ;
UNION 返回各个查询检索出的所有行，不包括重复的行
INTERSECT 返回两个查询检索出的共有行
MINUS 返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录
第一个查询检索出的行-第二个查询检索出的行
当使用集合操作符的时候，限制条件：
所有查询所返回的列数以及类型必须匹配，列名可以不同。
7.2使用TANSLATE()函数
TRANSLATE(x,from_string,to_string)函数在x中查找from_string中的字符，并将其转换成to_string中对应的字符。
7.3使用DECODE()函数
DECODE(value,search_value,result,default_value)对value与search_value进行比较。如果这两个值相等，DECODE()返回result,否则就返回dafault_value。
DECODE()允许在SQL中执行if-then-else类型的逻辑处理，而不需要使用PL/SQL。
Select decode(1,1,2,3) from dual;
结果为2.
select decode(null,1,2,3,4,5) from dual;结果为5
select decode(3,1,2,3,4,5) from dual; 结果为4
7.4 使用CASE表达式
工作方式与DECODE()类似，但我们应该用CASE，因为它与ANSI兼容。
有两种类型的CASE表达式，使用表达式确定返回值
搜索CASE表达式，使用条件确定返回值
7.4.1使用简单CASE表达式
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
…
WHEN expressionN THEN result
ELSE default_result
END
create table products(
id1 number,
id2 number);

insert into products values(111,1);
insert into products values(222,2);

select * from products;

select id1,id2,
case id2
when 1 then 'book'
when 2 then 'video'
else 'other'
end aliasName
from products;
7.4.2使用搜索CASE表达式
搜索CASE表达式使用条件确定返回值，语法：
CASE
WHEN condition1 THEN result1
WHEN condition1 THEN result1

…
WHEN condition1 THEN result1
ELSE default_result
END
如果condition1为真，则返回result1,依次类推
例子：
select id1,id2,
case
when id2=1 then 'book'
when id2=2 then 'video'
else 'other'
end briefName
from products;
7.5层次化查询
在组织为层次结构的数据中
7.5.2
使用CONNECT BY和START WITH子句
Select employee_id, manager_id,first_name,last_name
From more_employees
Start with employee_id=1
Connect by prior employee_id=manager_id;
7.5.3使用伪列LEVEL
Select count(distinct level)
From more_employees
Start with employee_id=1
Connect by prior employee_id=manager_id;
7.5.4格式化层次化查询的结果
LPAD(‘ ‘,2*(LEVEL-1))在左边填充2*(LEVEL-1)个空格。这种方法可以根据不同LEVEL填充不同个数的空格。
Set pagesize 999
Column employee format A25
Select level,
LPAD(‘ ‘,2*(LEVEL-1))||first_name||’ ‘||last_name as employee
From more_employee
Start with employee_id=1
Connect by prior employee_id=manager_id;
可以在Start with里使用子查询
7.5.7从下向上遍历树
不一定非要按照从父节点到子节点的顺序从上至下遍历树；也可以从某个子节点开始，自下向上进行遍历。
实现的方法是交换父节点与子节点在connect by prior 子句中的顺序。
7.5.8从层次查询中删除节点和分支
可以用WHERE子句从查询树中除去某个特点的节点，下面这个查询使用WHERE last_name!=’Johnson’子句从结果中除去Ron Johnson:
Column employee format A25
Select level,
LPAD(‘ ‘,2*(LEVEL-1))||first_name||’ ‘||last_name as employee
From more_employee
Where last_name!=’Johnson’
Start with employee_id=1
Connect by prior employee_id=manager_id;

为了将整个分支都从查询结果中除去，可以在CONNECTION PRIOR子句中使用AND子句。
下面这个查询使用AND last_name!=’Johnson’,这样可以将Ron Johnson 及其所有下属从结果中除去：Select level,
LPAD(‘ ‘,2*(LEVEL-1))||first_name||’ ‘||last_name as employee
From more_employee
Start with employee_id=1
Connect by prior employee_id=manager_id
And last_name!=’Johnson’;
7.5.9在层次化查询中加入其他条件
使用WHERE子句可以在层次化查询中加入其它条件。下面这个例子使用where子句来控制只显示工资少于或等于$50,000的员工。
Select level,
LPAD(‘ ‘,2*(LEVEL-1))||first_name||’ ‘||last_name as employee,salary
From more_employee
Where salary<=50000
Start with employee_id=1
Connect by prior employee_id=manager_id
7.6使用扩展的GROUP BY子句
7.7使用ROLLUP子句
ROLLUP子句扩展GROUP BY 子句，为每一个分组返回一条小计信息，并为全部分组返回总计。
Select division_id,sum(salary)
From employees
Group by RollUp(division_id);
DIVISION_ID SUM(SALALRY)
----------- ------------
A 1
B 2
3
最后一条记录包含所有分组的工资总计。
向ROLLUP传递多列
可以向ROLLUP传递多列，然后就会将这些记录分组成列值相同的块。
ROLLUP与其他的聚合函数一起使用，例如AVG()。
7.7.1使用CUBE子句
7.7.2使用GROUPING()函数
GROUPING()函数可以接受一列，返回0或者1.如果列值为空，那么GROUPING()返回1，如果列值非空，则返回0.GROUPING()只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值，GROUPING()就非常有用。
Select GROUPING(division_id), division_id,sum(salary)
From employees
Group by RollUp(division_id);
--------------- ----------- ------------
0 A 1
0 B 2
1 3
DECODE()函数可用于将前面例子中的那个1转换成其他有意义的值。
Select DECODE(GROUPING(division_id), 1,’All divisions’,division_id) as div,sum(salary)
From employees
Group by RollUp(division_id);
----------- ------------
A 1
B 2
’All divisions 3
7.7.3使用GROUPING SETS子句可以只返回小计记录。
7.7.4使用GROUP_ID()函数
可以使用它借助HAVING子句对记录进行过滤，将不包含小计或者总计的记录除去。
GROUP_ID()函数可用于消除GROUP BY 子句返回的重复记录。GROUP_ID()不接受任何参数。
如果某个特定的分组重复出现n次，那么GROUP_ID()返回从0到n-1之间的一个整数。
可以用HAVING子句消除重复记录。