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

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; ---->如果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 )

第三种方法:分组，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: 在块中的行号