ORACLE查询基础

例题：如何删除表中重复的记录？

 --建表
create   table   a   (   
  bm   char(4), --编码   
  mc   varchar2(20) --名称   
 )   
 
--插入记录         
 insert   into   a   values('1111','0001'); 
 insert   into   a   values('1112','0002');  
 insert   into   a   values('1113','0003');  
 insert   into   a   values('1114','0004');  
  
--制造4条重复记录
 insert into a select * from a;

--(找到重复记录)8条记录的后四条 
 select rowid ,a1.bm,a1.mc 
from a a1
where a1.rowid=(
 select max(a2.rowid)
  from  a a2
  where a2.bm=a1.bm
   and a2.mc=a1.mc
)

--删除重复记录   
 delete from a a1
  where a1.rowid != (select max(rowid)
                      from a a2
                     where a1.bm = a2.bm
                       and a1.mc = a2.mc);


--注意：找到重复记录的方法和下面的关联查询的区别
--16条记录
 select a1.rowid,a2.rowid,a1.*,a2.*
  from  a a1,a a2
  where a2.bm=a1.bm
   and a2.mc=a1.mc
   

例题，
分组查询：
按部门求每个部门薪水的前2名？

create table DEPT
(
  DEPTNO   VARCHAR2(20),
  DEPTNAME VARCHAR2(20)
)
;


create table EMP
(
  ENO    VARCHAR2(20),
  ENAME  VARCHAR2(20),
  SAL    VARCHAR2(20),
  DEPTNO VARCHAR2(20)
)
;


insert into DEPT (DEPTNO, DEPTNAME)values ('001', '开发一部');
insert into DEPT (DEPTNO, DEPTNAME)values ('002', '开发二部');
commit;

insert into EMP (ENO, ENAME, SAL, DEPTNO)
values ('00001', '谷超', '2500', '001');
insert into EMP (ENO, ENAME, SAL, DEPTNO)
values ('00002', '武立海', '1000', '001');
insert into EMP (ENO, ENAME, SAL, DEPTNO)
values ('00003', '张三', '2000', '002');
insert into EMP (ENO, ENAME, SAL, DEPTNO)
values ('00004', '李四', '4000', '002');
insert into EMP (ENO, ENAME, SAL, DEPTNO)
values ('00005', '赵柳', '5000', '001');
insert into EMP (ENO, ENAME, SAL, DEPTNO)
values ('00006', '王八', '5000', '001');
commit;

1.分组求出每个部门薪水最高的前2名雇员(普通sql方法查询)

select deptno, ename, sal       
from emp e1      
where       
   (   
    select count(1)      
    from emp e2      
    where e2.deptno=e1.deptno and e2.sal>=e1.sal   
   ) <=2
order by deptno, sal desc;  

2.分组求出每个部门薪水最高的前2名雇员，(利用分区功能)

select * from       
(select deptno,ename,sal,row_number() over (partition by deptno      
    order by sal desc) rn       
from emp)       
   where rn<=2;
 

备注：
   --row_number() 自然排序，无并列排序  1，2，3
   --rank() 有并列排名，两个第一名，接着是第三名  1，1，3
   --dense_rank() 有并列排名，两个第一名，接着是第二名 1，1，2





例二：
建立三张表：注意学生选课程时候一门课程只选择一次，这和查询的时候具体的操作密切相关

create table s(
 sno number(4) primary key ,
 sname varchar2(8)
);

create table c(
 cno number(4) primary key,
 cname varchar2(8),
 teacher varchar2(8)
);

create table sc(
  sno number(4) references s(sno),
  cno number(4) references c(cno),
  score number(2)
);


insert into s values (1101,'zhangs');
insert into s values (1102,'lisi');
insert into s values (1103,'wangwu');
insert into c values (1,'物理','teacher6');
insert into c values (2,'化学','teacher2');
insert into c values (3,'中文','teacher3');
insert into c values (4,'体育','teacher5');


insert into sc values (1101,1,61);
insert into sc values (1101,4,64);
insert into sc values (1102,1,66);
insert into sc values (1102,2,53);
insert into sc values (1102,4,34);
insert into sc values (1103,1,84);
insert into sc values (1103,2,55);
insert into sc values (1103,3,65);
insert into sc values (1103,4,24);

commit;



--统计每门课程被多少人选择了;
select cno ,count(cno) 
from sc 
group by cno


--选择2门以上课程的学生学号

select sno
	from sc
	group by sno
	having count(sno)>2


--选择2门以上课程的学生信息

select * 
from s
where exists (
 select 'x'
 from sc
 where sc.sno=s.sno 
  group by sc.sno
  having count(sc.sno)>2
)

select * 
from s
where s.sno in(
	select sno
	from sc
	group by sno
	having count(sno)>2
)


--没有选择'teacher2'所授课程的学生的信息？

-错误的
select sc.sno from sc 
where sc.cno not in
(select c.cno from c where c.teacher='teacher2')
-正确的
select *
from s
where s.sno not in
(select sno 
from sc
where cno='2')

select *   
from s   
where  not exists  
(select 'x'    
from sc  
where cno='2'and sc.sno= s.sno 
) 

select * 
from s
where not exists(--注意 not exsits
 select 'x'
 from sc
 where  exists(--注意  exsits
  select 'y'
  from c
  where sc.cno=c.cno and sc.sno=s.sno and c.teacher='teacher2'
 
 )

)



--选择了所有课程的学生的学号(分两步：a.统计每个学生所学的课程；b.统计所有课程数)
select sc.sno from sc group by sc.sno having count(sc.sno) --统计每个学生所学的课程
       =(select count(*) from c)--统计所有课程数

--所有学生都选择的课程

方法一：
select * from c where cno in(
select cno
from sc
group by cno 
having count(cno)  --统计每门课程被选择数		
= (select count(*) from s)--统计所有学生数
)

方法二：
select * 
from c 
where exists(   
  select 'x'   
  from sc  
  where sc.cno=c.cno 
  group by cno    
  having count(cno)  --统计每门课程被选择数        
  = (select count(*) from s)--统计所有学生数   
) 

方法三：
select * from 
(select c.cno,c.cname,count(sc.sno) as c_s
from sc,c
where sc.cno=c.cno
group by c.cno,c.cname) a,		--统计每门课程被选择数		
(select count(*) as all_s from s)b  --统计所有学生数
where a.c_s=b.all_s



例子2：
[color=red][size=medium]创建一张销售交易表：（注：一个人可以购买多种商品；一种商品可以被多个人购买；一个人可以多次购买同一种商品，但是数量随意[/size]）
[/color]
[color=darkred][size=medium]与例1不同的地方就在一个人可以多次购买同一件商品，也就是说同一个购买者和同一商品在数据表中可以有多条记录；但是例1中学生选择课程，在SC表中是不可能有重复的记录的；[/size]
 [/color]

商店交易数据表deal:
购买人:buyer   商品名称:product_name    数量:num
A, 甲, 4				
A, 乙, 6								
B, 丙, 3								
B, 丁, 9								
C, 甲, 7								
C, 丁, 2								
A, 丙, 5								
C, 丁, 1								
B, 丙, 5								
A, 乙, 2	
							
 
create table deal ( --交易
 buyer varchar2(4),
 product_name  varchar2(20),
 num   number(3)
);

--插入数据(一个人可以购买多种商品；一种商品可以被多个人购买；一个人可以多次购买同一种商品，但是数量随意)
insert into deal values('A','甲',4);
insert into deal values('A','乙',6);
insert into deal values('B','丙',3);
insert into deal values('B','丁',9);
insert into deal values('C','甲',7);
insert into deal values('C','丁',2);
insert into deal values('A','丙',5);
insert into deal values('C','丁',1);
insert into deal values('B','丙',5);
insert into deal values('A','乙',2);
commit;



--每个人购买每种商品的数量
select buyer,product_name,sum(num)
from deal
group by buyer,product_name

--购买2中以上商品的购物者
select t.buyer
from 
(select buyer,product_name
from deal
group by buyer,product_name
)t
group by t.buyer 
having count(t.buyer)>2


--销售的数量最大的商品

--方法一，普通方式：
select * from(
  select d.product_name as name,sum(num) as total   
  from deal d   
  group by d.product_name
  order by total desc
)
where rownum=1

--方法二，普通方式：

select d.product_name as name,sum(num) as total
from deal d
group by d.product_name
having sum(num)=
(
select max(t.total)
from
(select d.product_name as name,sum(num) as total
from deal d
group by d.product_name
) t
)

--方法三：（视图方式）

create view  v$_deal as (
  select d.product_name as name,sum(num) as total
  from deal d
  group by d.product_name
)

select v$_deal.name,v$_deal.total
from v$_deal
where v$_deal.total=(
select max(v$_deal.total)
from v$_deal
)