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,'Sm