碰到就更新:
1、排序
1.1按指定的顺序排序
order by decode(column, 'value1', 1, 'value2', 2, 'valued', 3, 'valueN', 99);
create table test(
id varchar2(32),
name varchar2(10),
age number,
varchar_date varchar2(20),
date_date Date
);
1、对于INSERT和UPDATE操作,oracle会把插入值或者更新值隐式转换为字段的数据类型。
insert into test values ('1','Jack','18') 相当于: insert into test values(,’1’,’Jack’,to_number('18'))
2、当比较字符型和数值型的值时,oracle会把字符型的值隐式转换为数值型。
select * from test where age='19' 相当于: select * from test where age=to_number('19')
select * from text where id=1; 相当于: select * from text where to_number(id)=1
注:如果id列建有索引此时将失效
3.当比较字符型和日期型的数据时,oracle会把字符型转换为日期型
select * from test where varchar_date > sysdate 相当于: select * from test where to_date(varchar_date,’yyyy-mm-dd hh24:mi:ss’)
select * from test where date_date>'2014-02-26 00:00:00' 相当于 select * from test where date_date>to_date('2014-02-26 00:00:00’, ’yyyy-mm-dd hh24:mi:ss’)
注意:1)当末发生隐式转换时索引有效(INDEX RANGE SCAN) 2)当字段列发生隐式转换时索引将失效,将使用全表扫描(TABLE ACCESS FULL)
总结:Oracle使用数据类型的优先级来决定隐式类型转换,原则是将优先级低的转换为优先级高的(数据类型优先级为:Number>字符类型>日期类型)。 隐式转换发生在字段列上时将使索引失效。
有一个表T,有两个字段a、b,我们想在表T中做Insert或Update操作,则可以使用Merge into函数:
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
用中文来解释上面的Merge语法,就是:
在alias2中Select出来的数据,每一条都跟alias1进行 ON (join condition)的比较,如果匹配,就进行更新的操作(Update),如果不匹配,就进行插入操作(Insert)。
因此,严格意义上讲,”在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数。”
oracle不支持update和insert操作时关联其他表,例如下面的代码在oracle中是无法执行的,遇到这种情况也可以使用merge into。
update a set a.xxx=b.eee from a left join b on a.bid=b.id where a.xxx='xxx'
4、分析函数-->over()
分析函数
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。
在主查询结果的基础上进行一定的分析,如分部门汇总,分部门求均值等等。
数据窗口
1. Oracle 分析函数建立在所谓的数据窗口之上,数据窗口可以理解为一个数据集合。主查询的数据可以按照不同的标准分割成不同的数据集。比如partition BY xx_id按照xx_id将主查询的数据分成N(N代表有多少个不同的xx_id)个不同的数据窗口。
2. 其次,数据窗口内部还应该与一定的顺序通过 ORDER BY 实现
over() 函数
是对分析函数的一种条件解释,可以理解为就是给分析函数加条件的函数
SUM :该函数计算组中表达式的累积和
MIN :在一个组中的数据窗口中查找表达式的最小值
MAX :在一个组中的数据窗口中查找表达式的最大值
AVG :用于计算一个组和数据窗口内表达式的平均值。
COUNT :对一组内发生的事情进行累积计数
-------------------------------------------------------------------------------------------------
RANK :根据ORDER BY子句中表达式的值,从查询返回的每一行,计算它们与其它行的相对位置
DENSE_RANK :根据ORDER BY子句中表达式的值,从查询返回的每一行,计算它们与其它行的相对位置
FIRST :从DENSE_RANK返回的集合中取出排在最前面的一个值的行
LAST :从DENSE_RANK返回的集合中取出排在最后面的一个值的行
FIRST_VALUE :返回组中数据窗口的第一个值
LAST_VALUE :返回组中数据窗口的最后一个值。
LAG :可以访问结果集中的其它行而不用进行自连接
LEAD :LEAD与LAG相反,LEAD可以访问组中当前行之后的行
ROW_NUMBER :返回有序组中一行的偏移量,从而可用于按特定标准排序的行号
-------------------------------------------------------------------------------------------------
STDDEV :计算当前行关于组的标准偏离
STDDEV_POP :该函数计算总体标准偏离,并返回总体变量的平方根
STDDEV_SAMP:该函数计算累积样本标准偏离,并返回总体变量的平方根
VAR_POP :该函数返回非空集合的总体变量(忽略null)
VAR_SAMP :该函数返回非空集合的样本变量(忽略null)
VARIANCE :如果表达式中行数为1,则返回0,如果表达式中行数大于1,则返回VAR_SAMP
COVAR_POP :返回一对表达式的总体协方差
COVAR_SAMP :返回一对表达式的样本协方差
CORR :返回一对表达式的相关系数
-------------------------------------------------------------------------------------------------
CUME_DIST :计算一行在组中的相对位置
NTILE :将一个组分为"表达式"的散列表示
PERCENT_RANK:和CUME_DIST(累积分配)函数类似
PERCENTILE_DISC:返回一个与输入的分布百分比值相对应的数据值
PERCENTILE_CONT:返回一个与输入的分布百分比值相对应的数据值
RATIO_TO_REPORT:该函数计算expression/(sum(expression))的值,它给出相对于总数的百分比
REGR_ (Linear Regression) Functions:这些线性回归函数适合最小二乘法回归线,有9个不同的回归函数可使用
5、分组排序函数-->rank/dense_rank/row_number() over()
函数的完整语句是:
select * from (select rank()/dense_rank()/row_number() over(partition by column1 order by column2 desc) rk,a.* from a) t
where t.rk<=1;
可以看出,over()函数可以包含3个子句:三个分析子句:分组(partition by), 排序(order by), 窗口(rows/range) 贴个例子来描述rank()/dense_rank()/row_number() over()三种函数的区别:
create table students_test
(id number(15,0),
nm varchar2(16),
course varchar2(16),
score number(5,2));
insert into students_test values(1, '张三', '语文', 80 );
insert into students_test values(2, '张三', '数学', 80 );
insert into students_test values(3, '张三', '英语', 89 );
insert into students_test values(4, '张三', '物理', 68 );
insert into students_test values(5, '李四', '语文', 80 );
insert into students_test values(6, '李四', '数学', 70 );
insert into students_test values(7, '李四', '英语', 60 );
insert into students_test values(8, '李四', '物理', 65 );
insert into students_test values(9, '王五', '语文', 75 );
insert into students_test values(10, '王五', '数学', 58 );
insert into students_test values(11, '王五', '英语', 58 );
insert into students_test values(12, '王五', '物理', 90 );
insert into students_test values(13, '赵六', '语文', 89 );
insert into students_test values(14, '赵六', '数学', 90 );
insert into students_test values(15, '赵六', '英语', 90 );
insert into students_test values(16, '赵六', '物理', 89 );
commit;
执行如下语句:
select t.nm,t.course,t.score,
rank() over(partition by course order by score desc) rank,
dense_rank() over(partition by course order by score desc) dense_rank,
row_number() over(partition by course order by score desc) row_number
from students_test t where course in ('数学','语文')
从上面的例子可以看出:
①ROW_NUMBER():
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②DENSE_RANK():
Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
③RANK():
Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
6、Oracle中in函数的陷阱
异常:ORA-01795: maximum number of expressions in a list is 1000
Oracle的In-list有1000个元素的限制。如果in中包含的元素个数超过1000,就会出现如上错误。
对于上述问题能够马上想到的解决办法有两个:
1、将元素拆分成多组不超过1000的组,一个in改成:in(...) or in(...) ...
2、使用exist替换
两个方法的比较:
1、改用多个的可读性强一些。但似乎也就只有这个有点,因为一般in中的元素超过1000,使用in的效率就不是很高了。
亲测的使用in(...) or in(...) ...的方式,1100个元素,耗时将近1秒。
2、对于查询表是小表的情况,exist的效率完爆in。否则还是建议使用方法1。具体为什么可以详细了解in和exist的区别。