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的内容
( 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的内容
解决方案 »
- order by加入后的奇怪问题,欢迎大家指教
- 问一下字段类型的问题decimal
- sql server 2000 的win2003版哪有??
- sqlserver 怎么控制存储过程只返回一个结果集,注意不要多个只要一个?
- 【急】DataTable -> SQL SERVER数据类型映射关系
- 大虾们,帮帮忙!!!!
- 请问SQL AGENT在同步复制里到底扮演什么角色?
- 数据库速度问题
- 本地搭建TRS数据库环境,主机ip连接出错。。请大神指教
- 如何用sql检索出两个表中关联字段,再放入新表中
- SQL字段内容批处理问题?在线等!马上给分!谢谢大家啦!
- 为什么要学习怎么用sql语句来创建表等操作,这些不是企业管理器里有现成的吗
where t1.id=t2.id
(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)
( 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)
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 )
如给出值16, ID为10,20两条记录;如给出值23, ID为20,30两条记录-----------
select * from t where id between (select top 1 id from t where id<16 order by id desc)
and (select top 1 id from t where id>16 order by id asc)
1.
select id,name,rand from dumpy_part
group by id,name,rand
having count(1)>=22.
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 desc6.
update a set a.col2=b.col2,a.col3=b.col3
from test1 a,test b
where a.id=b.id