oracle比较decode(case when) 、4种去重---和去重中的统计函数.doc

oracle比较decode/case when 、4种去重和去重中的统计函数一： decode 和 case whendecode (expression, search_1, result_1, search_2, result_2, ., search_n, result_n, default) decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。 select username,decode(lock_date,null,unlocked,locked) status from t; -如果lock_date是null就返回unlocked 如果不是null就返回lockedselect username,decode(lock_date,null,unlocked) status from t;-如果lock_date是null就返回unlocked 否则是空(因为没有定义)例如有个学生表. 行转列-create table score2(name varchar2(10),Language number(3),Math number(3),English number(3); insert into score2 values(Zhang,80,67,95);insert into score2 values(Li,79,84,62);insert into score2(name,Language) values(Chen,88);commit; Select * from score2; 显示成报表的格式.select name,sum(decode(subject,Language, grade,0) Language,sum(decode(subject,Math, grade,0) Math,sum(decode(subject,English, grade,0) Englishfrom scoregroup by name;NAME Language Math English- - - -Zhang 80 92 76Wang 73 0 0Li 81 95 50第二列，如果 subject=Language,那么就显示成绩,否则显示为0第三列，如果 subject=Math,那么就显示成绩,否则显示为0第四列，如果 subject=English,那么就显示成绩，否则显示为0Case when Case selector -selector可以不设置 When id=1 THEN id是1; ELSE 没有id值;END CASE; -如果ID=1 就显示id是1 否则显示没有id值上面用case when显示.select , sum( case when t.subject = Language then t.grade else 0 end ) dd, sum( case when t.subject = Math then t.grade else 0 end ) Math, sum( case when t.subject = English then t.grade else 0 end ) English from score t group by ;二：去重的4中方法create table test (c1 int ,c2 varchar2(10);insert into test values (1,Smith);insert into test values (1,Smith);insert into test values (2,John);insert into test values(1,Jack);insert into test values (2,Jack);insert into test values (2,John);insert into test values (2,John);commit;一种方法： distinct 把之前的表去重显示并创建,drop table old_table;create table tmp_test as select distinct * from test1; -创建临时表drop table test1;alter table tmp_test rename to test1;第二种 rowid delete from test where rowid ( select min(rowid) from test b where b.c1 = test.c1 and b.c2 = test.c2 )第三种方法:分组，rowiddelete from test t where t.rowid not in (select min(rowid) from test group by c1,c2 );commit;Rowid为伪列 是物理地址OOOOOO: 数据库对象号FFF: 表空间相关的数据文件号(相对文件号)BBBBBB: 数据块号RRR: 在块中的行号第四种方法，分析函数 dense_rank()drop table test;create table test (c1 int ,c2 varchar2(10);insert into test values (1,Smith);insert into test values (1,Smith);insert into test values (2,John);insert into test values(1,Jack);insert into test values (2,Jack);insert into test values (2,John);insert into test values (2,John);commit;select c1,c2,rowid rd,row_number() over(partition by c1,c2 order by c1) rn from test;不重复的只有1重复的就会出现2，3，4第一次出现，不重复的时候，rn为1相同的记录，重复出现，第二次，就记录为2第三次，3怎么找不出重复的行select b.c1,b.c2 from 和县在线:/ (select c1,c2,rowid rd,row_number() over(partition by c1,c2 order by c1) rn from test) b where b.rn = 1;不等于号 用了不走索引 效率很低分页 row_number要求emp表中的5-10的记录？select * from(select a.*,row_number() over(order by empno desc) rk from emp a) where rk=5;select rn,empno,ename from (select rownum rn,empno,ename from emp) where rn=5 and rn=10;select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rk from emp) where rk发现部门为20的scott 和ford都是3k，所以并列第一 有2.3名select * from (select deptno,ename,sal,rank() over(partition by deptno order by sal desc) rk from emp ) where rk发现部门为20的scott 和ford都是3k，所以并列第一 没2有3名Partition by = group by 分析函数里用的是 partition by 普通函数 用的是 group by select * from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn from emp) where rn发现部门为20的scott 和ford按照默认显示出现1和2 名次那么看下这些区别：select * from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc)