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 https://www.wendangku.net/doc/0012440570.html,,
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 https://www.wendangku.net/doc/0012440570.html,;
二：去重的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
宣城在线:https://www.wendangku.net/doc/0012440570.html,/
(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;
select *
from (
select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rk
from emp)
where rk<=3; --->发现部门为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<=3; --->发现部门为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<=3; --->发现部门为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;
https://www.wendangku.net/doc/0012440570.html,/blog/1546668
Oracle统计函数