select distinct url into #a from WorkInfo_UrlTemp--把不重复的纪录放到临时表里 delete WorkInfo --清除表所有数据 select * into WorkInfo from #a--把不重复纪录放回原表里 drop table #a--删掉临时表两种同步方法:同步方法(用synchronized修饰方法) synchronized f(){ ..... } 同步块 synchronized{ .... }
其实有没有更简单一点的SQL语句呢?期待。。 帮楼主顶上去。
如果是ORACLE,可以这样写(我写的是select,如果是删除,则把第一个SELECT改成delete):select a.rowid,a.c1 from test1 a where not exists (select 1 from ( select a.rid as rid from (select b.rowid as rid,b.c1 as c1,a.groupid as groupid,rownum as rno from test1 b, (select c1 as c1,rownum as groupid from (select c1 as c1 from test1 group by c1) a) a where b.c1=a.c1 order by b.c1) a, (select c1,max(rno) as rno from (select b.rowid as rid,b.c1 as c1,a.groupid as groupid,rownum as rno from test1 b, (select c1 as c1,rownum as groupid from (select c1 as c1 from test1 group by c1) a) a where b.c1=a.c1 order by b.c1) a group by c1) b where a.c1=b.c1 and a.rno=b.rno) b where a.rowid=b.rid)
在实际中,如果谁这样写SQL,那一定是个疯子!!
delete table id in ( select max(id) from table where 相同数据的字段 in ( select distinct 相同数据的字段 from table )
可能有些数据库提供类似功能的SQL,但是ORACLE没有。
sqlserver:delete from table where id in (select id from table group by id having count(id)>1)
留下一条的:oracle: create table tmp_table as select distinct * from table where id in ( select id from table group by id having count(id)>1)delete from table where id in (select id from table group by id having count(id)>1)insert into table select * from tmp_tablesqlserver: select distinct * into tmp_table from table where id in ( select id from table group by id having count(id)>1)delete from table where id in (select id from table group by id having count(id)>1)insert into table select * from tmp_table
删除重复记录 DELETE FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2)
将所有记录与不重复的记录做一个差集 select * from table minus select distinct * from table第二个我认为应该有两种 synchronized lock...unlock
表有主键id: 1、保留重复记录的第一条 DELETE FROM tmp where id!=( SELECT max(id) FROM tmp D WHERE tmp.fname=D.fname ) 2、保留重复记录的最后一条 DELETE FROM tmp where id!=( SELECT min(id) FROM tmp D WHERE tmp.fname=D.fname ) 表无主键方法有2,但是都是通过临时表: 1、建立索引 先在重复记录上建立索引,忽略重复记录,然后 select * into #tmp from tmp delete from tmp setect * into tmp from #tmp drop table #Tmp 2、查询语句 select distinct * into #Tmp from tmp delete from tmp select * into tmpfrom #Tmp drop table #Tmp
1.SELECT DISTINCT * INTO tempTable FROM tableName 2.把原来的表删掉 3.重新命名新表同步有两种方法
1.删除表中的重复记录(写出SQL语句)-------------->验证通过 1).建立一个临时表,用于存放原表中的重复数据 creat table tem select *,COUNT* from cat group by id,name,sex,weight having count(*)>1 2).删除原表中数据和临时表中的数据相同的字段 delete from user where userid+username+sex+address in (select userid+username+sex+address from tem1) 3).合并临时表和原表 insert into user select * from tem 4).删除临时表 drop table tem2,线程的同步有几种方法。 sleep,synchronized.wait
2、2种方法(1)同步整个方法,也就是锁定this (2)锁定一个对象
delete WorkInfo --清除表所有数据
select * into WorkInfo from #a--把不重复纪录放回原表里
drop table #a--删掉临时表两种同步方法:同步方法(用synchronized修饰方法)
synchronized f(){
.....
}
同步块
synchronized{
....
}
帮楼主顶上去。
where not exists (select 1 from (
select a.rid as rid from
(select b.rowid as rid,b.c1 as c1,a.groupid as groupid,rownum as rno from test1 b,
(select c1 as c1,rownum as groupid from (select c1 as c1 from test1 group by c1) a) a
where b.c1=a.c1
order by b.c1) a,
(select c1,max(rno) as rno from
(select b.rowid as rid,b.c1 as c1,a.groupid as groupid,rownum as rno from test1 b,
(select c1 as c1,rownum as groupid from (select c1 as c1 from test1 group by c1) a) a
where b.c1=a.c1
order by b.c1) a
group by c1) b
where a.c1=b.c1 and a.rno=b.rno) b where a.rowid=b.rid)
(
select max(id) from table where 相同数据的字段 in
(
select distinct 相同数据的字段 from table )
你错了,你把不该杀的也杀了!
比如有
1
1
1
按题目应该留一条的,结果你一条也没留
题目是
1.删除表中的重复记录(写出SQL语句)难道说了应该留一条吗?
create table tmp_table as
select distinct * from table where id in (
select id from table group by id having count(id)>1)delete from table where id in (select id from table group by id having count(id)>1)insert into table select * from tmp_tablesqlserver:
select distinct * into tmp_table from table where id in (
select id from table group by id having count(id)>1)delete from table where id in (select id from table group by id having count(id)>1)insert into table select * from tmp_table
DELETE FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2)
select * from table
minus
select distinct * from table第二个我认为应该有两种
synchronized
lock...unlock
1、保留重复记录的第一条
DELETE FROM tmp where id!=(
SELECT max(id) FROM tmp D WHERE tmp.fname=D.fname )
2、保留重复记录的最后一条
DELETE FROM tmp where id!=(
SELECT min(id) FROM tmp D WHERE tmp.fname=D.fname )
表无主键方法有2,但是都是通过临时表:
1、建立索引
先在重复记录上建立索引,忽略重复记录,然后
select * into #tmp from tmp
delete from tmp
setect * into tmp from #tmp
drop table #Tmp
2、查询语句
select distinct * into #Tmp from tmp
delete from tmp
select * into tmpfrom #Tmp
drop table #Tmp
2.把原来的表删掉
3.重新命名新表同步有两种方法
1).建立一个临时表,用于存放原表中的重复数据
creat table tem select *,COUNT* from cat group by id,name,sex,weight having count(*)>1
2).删除原表中数据和临时表中的数据相同的字段
delete from user where userid+username+sex+address in (select userid+username+sex+address from tem1)
3).合并临时表和原表
insert into user select * from tem
4).删除临时表
drop table tem2,线程的同步有几种方法。
sleep,synchronized.wait