【道森试听】sql基础——飞雪老师

--Case when 相当于 if else
select ename,sal,
case deptno 
when 10 then '会计部' 
when 20 then '研究部' 
when 30 then '销售部' 
else '其他部门' 
end 部门 
from emp ;
--根据部门编号，
--如果部门编号为10的，显示为会计部
--如果部门编号为20的，显示为研究部
--如果部门编号为30的，显示为销售部
--否则显示为其它部门
--这一列查询的结果，列名显示为 “部门”


--decode(条件,值1,结果1,值2,结果2,...值n,结果n,缺省值)
select ename,sal,
decode(deptno,10,'会计部',20,'研究部',30,'销售部','其他部门') 部门 
from emp;


--工资分级
select ename,sal,
case
when sal>0 and sal<=1500 then '一级工资' 
when sal>1500 and sal<=3000 then '二级工资' 
when sal>3000 and sal<=4500 then '三级工资' 
else '四级工资' 
end 工资等级 
from emp 
order by sal desc;
--规律：工资等级之间差1500，用trunc函数分级（trunc()截取数字）
select ename,sal,trunc((sal-1)/1500) from emp;
--用decode和trunc显示工资等级
select ename,sal,trunc((sal-1)/1500),
       decode(trunc((sal-1)/1500),
  0,'1级工资',
              1,'2级工资',
              2,'3级工资',
          '4级工资') 等级
from emp
order by sal desc;
--sign()取符号，大于0返回1，小于0返回-1，等于0返回0
select ename,sal,decode(sign(sal-2000),1,'大于2000',-1,'小于2000','等于2000') 工资等级
from emp;
--总结:
decode和case when都是用于条件判断
但decode用于等值判断（一些有规律的，可以变化一下，变成等值判断）
case when语句复杂一些，但判断的条件更灵活，更方便,范围更广


--行列转换
drop table score;
create table score(
    name varchar2(10),
    subject varchar2(10),
    grade number(3)
)
insert into score values('Zhang','Language',80);
insert into score values('Zhang','Math',92);
insert into score values('Zhang','English',76);
insert into score values('Li','English',50);
insert into score values('Li','Math',95);
insert into score values('Li','Language',81);
insert into score values('Wang','Language',73);
commit;

select * from score;

--列转行
--统计每个人各科目的成绩
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;
--第一列，如果 subject='Language',那么就显示成绩,否则显示为0
--第二列，如果 subject='Math',那么就显示成绩,否则显示为0
--第三列，如果 subject='English',那么就显示成绩,否则显示为0
--比较不加sum
select name,
decode(subject,'Language', grade,0) "Language",
decode(subject,'Math', grade,0) "Math",
decode(subject,'English', grade,0) "English"
from score
--用case when来写
SELECT name,
SUM(CASE WHEN subject='Language' THEN grade ELSE 0 END) AS "Language",
SUM(CASE WHEN subject='Math' THEN grade ELSE 0 END) AS "Math",
SUM(CASE WHEN subject='English' THEN grade ELSE 0 END) AS "English"
FROM score
group by name;


--行转列
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 name, 'Language' subject,Language grade FROM score2
UNION ALL
SELECT name, 'Math' subject,Math grade FROM score2
UNION ALL
SELECT name, 'English' subject,English grade FROM score2
order by name;


--去除重复行
--使test无重复
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;
drop table test11;
create table test11 as select * from test;
--1.distinct 适合重复数据少，要保留的数据非常少，做此操作需要停业务！
create table tmp_test as select distinct * from test11;
select * from tmp_test;
drop table test11;
alter table tmp_test rename to test11;
select * from test11;
--2.rowid
delete from test
    where rowid <> ( select min(rowid)
                     from test b
                     where b.c1 = test.c1
                       and b.c2 = test.c2 )
select * from test;
--3.方法:分组，rowid
delete from test t 
where t.rowid not in (select min(rowid) from test group by c1,c2 );
--4.分析函数
delete from test where rowid not in
(select rowid from 
    (select c1,c2,rowid rd,row_number() over(partition by c1,c2 order by c1) rn 
     from test) b 
where b.rn = 1);
--找出不重复的行
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;


--分析函数——排序函数
select deptno,max(sal)
from emp
group by deptno


select ename,sal from emp 
where sal in(
select max(sal)
from emp
group by deptno);


--每个部门的工资前三名
--dense_rank()
select * 
from (
select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rk 
from emp) 
where rk<=3;
--rank()
select * 
from (
select deptno,ename,sal,rank() over(partition by deptno order by sal desc) rk 
from emp ) 
where rk<=3;
--跟聚焦区分，就要over。分析函数partition by类似于group by，只用在over后面的括号内。
--row_number() over()
select * 
from(
select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn 
from emp) 
where rn<=3;--scott在ford前面，所以scott为第一，ford为第二。
--三种排序对比
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;


--分页
--top N（sql server 中有，oracle没有）

--row_number（oracle）

--要求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 rownum from emp;--row_number执行一次，才会编号
select * from emp where rownum>5;--无记录返回！为什么？不能大于，只可以小于等于。因为从1号开始编号。要先编号（扫描表——做个子查询），才能找到。


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


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


--分析函数——sum over  （加over就是分析函数）
select deptno,ename,sal,sum(sal) over()
from emp;--所有总和


select deptno,ename,sal,sum(sal) over(partition by deptno) 
from emp;--部门总和


select deptno,ename,sal,sum(sal) over(partition by deptno order by sal) 
from emp;--一个部门前几行累加

--开窗子句。有order by, 默认的开窗子句是unbounded preceding and current row 截止到当前行，汇总。（累计前面的行到当前行）

-- unbounded preceding and unbounded following前后都无边界
select deptno,ename,sal,sum(sal) over(partition by deptno order by sal
rows between unbounded preceding and unbounded following) 

from emp;

-- rows between 1 preceding and current row当前行与上一行汇总
select deptno,ename,sal,sum(sal) over(partition by deptno order by sal
rows between 1 preceding and current row) 
from emp;
--可按rows（行）、range（范围）、偏移量开窗