oracle:高级查询

一.使用集合操作符
1.UNION ALL：返回各个查询检索出的所有行，包括重复的行
2.UNION：返回查询检索出的所有非重复行
3.INTERSECT：返回两个查询检索出的共有行
4.MINUS：返回从第一个查询检索出的行中减去第二个查询检索出的行之后剩余的行。
二.TRANSLATE()函数
TRANSLATE(x,from,to):在x中查找from中的字符，并将其转换成to中对应的字符

select TRANSlATE('hello world', 'abcdefg', 'hijklmn') as test from dual;

TEST
-----------
hlllo worlk

三.DECODE()
DECODE(value,search,result,default):对value与search进行比较。如果两个值相等，就返回result，否则返回default

注意：DECODE是oracle特有的函数，9i或者更高版本，用case代替。

select DECODE(1,2,3,4) from dual;

DECODE(1,2,3,4)
------------------
4

四.CASE
CASE 表达式是在SQL中实现if-then-else逻辑。
简单CASE表达式，使用表达式确定返回值。
搜索CASE表达式，使用条件确定返回值。
简单CASE表达式:
语法如下：

CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
ELSE default_result
END

搜索CASE表达式:
语法如下：

CASE 
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END

五：层次化查询：
树
使用CONNECT BY和START WITH字句
语法：

SELECT [LEVEL], column, expression, ...
FROM table
[WHERE where_clause]
[[START WITH start_conditon][CONNECT BY PRIOR prior_condition]

六：使用ROLLUP和CUBE子句
ROLLUP：为每个分组返回小计行，同时为所有分组返回总计行
CUBE：返回每一列组合的小计行，同时在末尾加上总计行
GROUPING()函数：可以接受一列，返回0或1，如果列值为空，返回1，如果列值非空，返回0
GROUPING SETS子句可以得到小计行。
GROUPING_ID()函数借助having子句对行进行过滤，将不包含小计或总计的行去除。
七：使用CROSS APPLY和OUTER APPLY
CROSS APPLY：返回从两条select语句合并的行。只有外部select和内部select匹配才在结果集中返回。
OUTER APPLY：返回从两条select语句合并的行。包括从外部select返回的不匹配的行。
八：使用LATERAL
提供一个子查询作为内联视图。
限制：
1.不能再引用表中与pivot和unpivot连用
2.内联视图不能包含对在右外连接或全外连接中的第一个表的左关联