oracle比较函数 decode/case when、4种去重方法、去重中的统计函数

 

一： decode  和 case when

decode (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就返回locked

select 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)) "English"

from score

group by name;

 

NAME                   Language       Math    English

-------------------- ---------- ---------- ----------

Zhang                        80         92         76

Wang                         73          0          0

Li                           81         95         50

第二列，如果 subject='Language',那么就显示成绩,否则显示为0

第三列，如果 subject='Math',那么就显示成绩,否则显示为0

第四列，如果 subject='English',那么就显示成绩，否则显示为0

Case when 

Case [selector] ---selector可以不设置 

When id=1 THEN ‘id是1’;

    ELSE ‘没有id值’;
END CASE;   ----&gt如果ID=1 就显示id是1  否则显示没有id值

~~~~~上面用case when显示.................
select t.name,
      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 t.name;

 

 

二：去重的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 )

 

第三种方法:分组，rowid

 

delete 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<=10 and rk>=5;

 select rn,empno,ename  from (select rownum rn,empno,ename from emp) where rn>=5 and rn<=10;

        RN      EMPNO ENAME
---------- ---------- ----------
         5       7566 JONES
         6       7654 MARTIN
         7       7698 BLAKE
         8       7782 CLARK
         9       7788 SCOTT
        10       7839 KING

6 rows selected.

select * 
from (
select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rk 
from emp) 
where rk<=3;   ---&gt发现部门为20的scott 和ford都是3k，所以并列第一   有2.3名

    DEPTNO ENAME             SAL         RK
---------- ---------- ---------- ----------
        10 KING             5000          1
        10 CLARK            2450          2
        10 MILLER           1300          3
        20 SCOTT            3000          1
        20 FORD             3000          1
        20 JONES            2975          2
        20 ADAMS            1100          3
        30 BLAKE            2850          1
        30 ALLEN            1600          2
        30 TURNER           1500          3
           CLERK                          1

11 rows selected.

 

select * 
from (
select deptno,ename,sal,rank() over(partition by deptno order by sal desc) rk 
from emp ) 
where rk<=3;    ---&gt发现部门为20的scott 和ford都是3k，所以并列第一  没2有3名

    DEPTNO ENAME             SAL         RK
---------- ---------- ---------- ----------
        10 KING             5000          1
        10 CLARK            2450          2
        10 MILLER           1300          3
        20 SCOTT            3000          1
        20 FORD             3000          1
        20 JONES            2975          3
        30 BLAKE            2850          1
        30 ALLEN            1600          2
        30 TURNER           1500          3
           CLERK                          1

10 rows selected.

 

Partition by == group by 

分析函数里用的是  partition by    

分析函数的一般格式是：

函数名(参数列表) over ([partition by 字段名或表达式] [order by 字段名或表达式])，其中over()部分称为开窗函数，它是可以选填的。

SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;

列出Col2分组后根据Col1排序,并生成数字列。

普通函数 用的是 group by 

select * 

from(

select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn  

from emp) 

where rn<=3;     ---&gt发现部门为20的scott 和ford按照默认显示出现1和2 名次

那么看下这些区别：

select * 
from(select deptno,ename,sal,
row_number() over(partition by deptno order by sal desc) rn,
rank() over(partition by deptno order by sal desc) rk, 
  dense_rank() over(partition by deptno order by sal desc) drk 
from emp)  where drk<=3;  

由此结论：

rank()跳跃排序，比如有两个第1名时接下来就是第3名（同样是在各个分组内）

dense_rank()连续排序，比如有两个第1名时仍然跟着第2名。

http://yanguz123.iteye.com/blog/1546668 Oracle统计函数  ...开窗函数...分析函数...

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28602568/viewspace-759791/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28602568/viewspace-759791/