Oracle通用函数 分组函数 子查询
发布时间：2010/8/20 10:50:00 来源：城市学习网 编辑：ziteng
注意：1）Oracle中的字符串是严格区分大小写的，日期和字符只能在单引号中出现；
2）把列与列，列与字符连接在一起用 ‘||’符号；
3）列的别名，紧跟列名，也可以在列名和别名之间加入关键字‘AS’，以便在别名中包含空格或特殊的字符并区分大小写，使用双引号。
例子：
SELECT last_name || ' is a ' || job_id AS "Employee Details"
FROM employees
where first_name like '%s_';
通用函数
空值是无效的，未指定的，未知的或不可预知的值，空值不是空格或者0 ，包含空值的数学表达式的值都为空值。
这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
（1）NVL (expr1, expr2) ->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
（2）NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
（3）NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1
（4）COALESCE(expr1, expr2, ..., exprn) ->列表中第一个非空的表达式是函数的返回值，如果所有的表达式都是空值，最终将返回一个空值。
条件表达式在SQL语句中使用IF-THEN-ELSE 逻辑。可以使用两种方法:–CASE表达式 CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
–DECODE函数 DECODE ( col | expression, search1, result1 [, search2, result2,...,] [, default] )
分组函数
分组函数作用于一组数据，并对一组数据返回一个值。
组函数语法：
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
注意：在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中；
(所用包含于SELECT列表中，而未包含于组函数中的列都必须包含于GROUPBY子句中)
但包含在GROUP BY 子句中的列不必包含在SELECT列表中；
且可以在在GROUP BY子句中包含多个列；
不能在WHERE子句中使用组函数；但可以在HAVING子句中使用组函数。
使用HAVING过滤分组：
1.行已经被分组；
2.使用了组函数；
3.满足HAVING子句中条件的分组将被显示；
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column]; [NextPage] 子查询
语法：
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
注意：子查询(内查询) 在主查询之前一次执行完成；
子查询的结果被主查询使用(外查询)；
子查询要包含在括号内；
将子查询放在比较条件的右侧；
除非进行Top-N 分析，否则不要在子查询中使用ORDER BY子句；
单行操作符对应单行子查询，多行操作符对应多行子查询。
单行操作符operator： 多行操作符operator ：
= 等于 IN 等于列表中的任何一个
> 大于 ANY 和子查询返回的任意一个值比较
>= 大于等于 ALL 和子查询返回的所有值比较
< 小于 EXISTS 功能等价于IN
<= 小于等于
<> 不等于
DECODE和CASE，SELECT中除GROUP BY 子句以外的所有子句中，都可以使用单行子查询。
在子查询中使用HAVING子句：
SELECT select_list
FROM table
[GROUP BY group_by_expression]
[HAVING group_condition] expr operator
(SELECT select_list
FROM table);
在FROM子句中使用子查询，例子：
SELECT a.last_name, a.salary, a.department_id, b.salavg
FROM employees a, (SELECT department_id, AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
有两个简单例子，以说明 “exists”和“in”的效率问题
1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;
T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。
2) select * from T1 where T1.a in (select T2.a from T2) ;
T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。
exists 用法：
请注意 1）句中的有颜色字体的部分 ，理解其含义；
其中 “select 1 from T2 where T1.a=T2.a” 相当于一个关联表查询，相当于
“select 1 from T1,T2 where T1.a=T2.a”
但是，如果你当当执行 1） 句括号里的语句，是会报语法错误的，这也是使用exists需要注意的地方。
“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。
因此“select 1”这里的 “1”其实是无关紧要的，换成“*”也没问题，它只在乎括号里的数据能不能查找出来，是否存在这样的记录，如果存在，这 1） 句的where 条件成立。 in 的用法：
继续引用上面的例子
“2) select * from T1 where T1.a in (select T2.a from T2) ”
这里的“in”后面括号里的语句搜索出来的字段的内容一定要相对应，一般来说，T1和T2这两个表的a字段表达的意义应该是一样的，否则这样查没什么意义。
打个比方：T1，T2表都有一个字段，表示工单号，但是T1表示工单号的字段名叫“ticketid”，T2则为“id”，但是其表达的意义是一样的，而且数据格式也是一样的。这时，用 2）的写法就可以这样：
“select * from T1 where T1.ticketid in (select T2.id from T2) ”
EXISTS操作符
EXISTS 操作符检查在子查询中是否存在满足条件的行
- 如果在子查询中存在满足条件的行：
– 不在子查询中继续查找
– 条件返回TRUE
- 如果在子查询中不存在满足条件的行：
– 条件返回FALSE
– 继续在子查询中查找
相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。子查询使用了主查询中的列。
SELECT column1, column2, ...
FROM table1 outer
WHERE columnk operator (SELECT colum1, colum2
FROM table2
WHERE expr= outer.expr);
相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据。
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
相关删除
使用相关子查询依据一个表中的数据删除另一个表的数据。
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
WITH子句
- 使用WITH子句，可以避免在SELECT语句中重复书写相同的语句块
- WITH子句将该子句中的语句块执行一次，并存储到用户的临时表空间中
- 使用WITH子句可以提高查询效率
我们可以使用WITH Clause来定义一个query block，然后在SELECT statement的其它地方使用这个query block。如果在一个很复杂的 Query 里，我们必须重复地使用某个 subquery，那么使用WITH Clause可以降低Query的复杂度以及提高 performance。因为WITH Clause 所读出的资料会暂存在User的temporary tablespace中。
WITH子句应用举例：
WITH
dept_costs AS (SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs) [NextPage] SELECT *
FROM dept_costs
WHERE dept_total > (SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
GROUP BY 扩展
带有ROLLUP和CUBE操作的GROUP BY子句
- 使用带有ROLLUP和CUBE操作的GROUP BY子句产生多种分组结果
- ROLLUP产生n + 1种分组结果，其是对GROUP BY子句的扩展
- CUBE产生2的n次方种分组结果，其是对GROUP BY子句的扩展
注：其中的n指的是group_by_expression的数目。
ROLLUP操作符 ：ROLLUP产生n + 1种分组结果，顺序是从右向左
SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
CUBE操作符：CUBE会产生类似于笛卡尔集的分组结果
SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
GROUPING函数：能够实现更加直观的分组结果显示提示
SELECT [column,] group_function(column) . , [GROUPING(group_by_expression)]..
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
- GROUPING函数可以和CUBE或ROLLUP结合使用
- 使用GROUPING函数，可以找到哪些列在该行中参加了分组
- 使用GROUPING函数, 可以区分空值产生的原因
- GROUPING函数返回0 或1
GROUPING SETS：
- GROUPING SETS是对GROUP BY子句的进一步扩充
- 使用GROUPING SETS可以实现在同一个查询中定义多个分组集
- Oracle 对GROUPING SETS子句指定的分组集进行分组后用UNION ALL操作将各分组结果结合起来
- Grouping set 的优点：
– 只进行一次分组即可
– 不必书写复杂的UNION语句
– GROUPING SETS中包含的分组项越多性能越好
以下例子实现了对department_id, job_id分组，对job_id, manager_id分组，最终形成两个分组：
SELECT department_id, job_id, manager_id, avg(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id));
复合列
- 复合列是被作为整体处理的一组列的集合
- 使用括号将若干列组成复合列在ROLLUP或CUBE中作为整体进行操作
- 在ROLLUP或CUBE中，复合列可以避免产生不必要的分组结果
例如：GROUP BY ROLLUP( department_id, (job_id, manager_id)); //小括号实现复合列
连接分组集
- 连接分组集可以产生有用的对分组项的结合
- 将各分组集、ROLLUP 和CUBE用逗号连接，Oracle自动在GROUP BY子句中将各分组集进行连接
- 连接的结果是对各分组生成笛卡尔集
例如：GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d);
例如：GROUP BY department_id, ROLLUP(job_id), CUBE(manager_id);
