几个面试题。请高手解决一下。谢谢
create table dumpy_part
( id number(10), name varchar2(10), rand number(10,2))
1.查询id重复记录
2．删除id重复记录，只保留第一条
-----------------------------
在EMP表中：
3.找到每个部门工资最高的人(只选一个)
4.找到每个部门工资最高的人(包括并列第一)
--------------------
表test(id int , name varchar2(10), addr varchar2(20))
记录： id name addr
10 rose Guangzhou
20 jack shanghai
30 may Beijing
5．查找记录，找出与给定值最接近的两条记录
如给出值16， ID为10，20两条记录；如给出值23， ID为20，30两条记录
6．有一表test1，结构与test相同，要求若id相同，将test1的col2，col3 替换test的col2，col3的内容
[解决办法]
6. update t1 set t1.col2=t2.col2,t1.col3=t2.col3 from test t1,test1 t2
where t1.id=t2.id
[解决办法]
1. select id,count(*) from dumpy_part group by id having count(*)> =2
[解决办法]
2. delete from dumpy_part where id not in
(select min(id) from dumpy_part group by id having count(*)> =2)
and id in (select id from dumpy_part goup by fid having count(*)> =2)
[解决办法]
create table dumpy_part
( id number(10), name varchar2(10), rand number(10,2))
1.查询id重复记录
2．删除id重复记录，只保留第一条
--------
1:
select id ,count(*) from t group by id hanving count(*)> 1
2:
delete t a
where exists (select 1 from t where id=a.id and rand <a.rand)
[解决办法]
在EMP表中：
3.找到每个部门工资最高的人(只选一个)
4.找到每个部门工资最高的人(包括并列第一)
---------
3:
select name from t where name=(select top 1 name from t a where gongzi=(select max(gongzi) from t where department=a.department ) order by name)
4:
select name from t a where gongzi=(select max(gongzi) from t where department=a.department )
[解决办法]
1.
select id,name,rand from dumpy_part
group by id,name,rand
having count(1)> =2
2.
delete a from dumpy_part a
where exists(select 1 from dumpy_part b
where a.name=b.name and a.rand=b.rand and a.id> b.id)
3.
select * from emp a
where not exists(select 1 from emp b where a.dept_id=b.dept_id and a.income <b.income and a.id <b.id)
4.
select * from emp a
where not exists(select 1 from emp b where a.dept_id=b.dept_id and a.income <b.income )
5.
declare @i int
set @i=16
select top 1 * from tabname
where id> @i
order by id
union all
select top 1 * from tabname
where id <@i
order by id desc
6.
update a set a.col2=b.col2,a.col3=b.col3
from test1 a,test b
where a.id=b.id
