如果确定id 和newid 都>0 的话可以这样.insert into a select distinct -1*id,-1*newid from a delete from a where id>0 update a set id=abs(id),newid=abs(newid)select * from a
select * from 表名 group by id,newid
copico得结果不正是楼主要得结果吗?? distinct就是用来筛选重复得数据得..测试如下. create table #tb(id int,newid int) insert into #tb(id,newid) values(1,1) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) values(1,1) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) values(3,3) insert into #tb(id,newid) values(3,3) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) values(2,2) select distinct * from #tbdrop table #tb 结果: id newid 1 1 2 2 3 3
我再把我的意思说一下,可能没有说清楚,我要把表中重复的数据删除掉,只留下 id newid 1 1 2 2 3 3 不能用临时表,该如何写SQL语句?
select distinct * from table 老大,这样怎么不行啦
insert into xx select distinct * from dd 导到另外一个表
回copico(路北) ,你这样是取出了数据,但没有把表中重复的数据删除掉
使用游标测试通过,仅供参考:declare @counts int declare @id int declare record cursor for select id,count(*) from Testtable group by id having count(*) > 1 open record fetch record into @id,@counts while @@fetch_status=0 begin select @counts = @counts -1 set rowcount @counts delete from Testtable where id = @id fetch record into @id,@counts end close record deallocate record set rowcount 0 select * from Testtable order by id
使用游标测试通过,仅供参考:declare @counts int declare @id int declare record cursor for select id,count(*) from Testtable group by id having count(*) > 1 open record fetch record into @id,@counts while @@fetch_status=0 begin select @counts = @counts -1 set rowcount @counts delete from Testtable where id = @id fetch record into @id,@counts end close record deallocate record set rowcount 0 select * from Testtable order by id
方法大体有: 1.游标。 上面有朋友写过了,我写的话也是差不多的,只是细节上略有差别,就不写了。 2.临时表或表变量 declare @t table(id int,newid int) insert @t select distinct id,newid from tb truncate table tb insert tb select id,newid from @t 3,加标识列 create table tb(id int,newid int) insert into tb(id,newid) values(1,1) insert into tb(id,newid) values(2,2) insert into tb(id,newid) values(2,2) insert into tb(id,newid) values(1,1) insert into tb(id,newid) values(2,2) insert into tb(id,newid) values(3,3) insert into tb(id,newid) values(3,3) insert into tb(id,newid) values(2,2) insert into tb(id,newid) values(2,2)alter table tb add idx int identity(1,1) go delete a from tb a where exists(select 1 from tb where idx<a.idx and checksum(a.id,a.newid)=checksum(id,newid)) go alter table tb drop column idx select * from tb go drop table tb go
你这个选择是没有意义的,后面newid不一样的话,你道底要显示哪一个33?????哪一个22????,哪一个11??????,所以sql里也不存在你这样不合理个要求,你要不重复的话只有这样,select distinct (id) from dd,
"你这个选择是没有意义的,后面newid不一样的话,你道底要显示哪一个33?????哪一个22????,哪一个11??????,所以sql里也不存在你这样不合理个要求,你要不重复的话只有这样,select distinct (id) from dd," 支持!!!
不清楚你得表数据情况是怎么样得. 能否采用这样得方法,比如你可以先获取到你得id和newid得最大值 然后设置一个变量大于你前面取到得最大值 再用select distinct id+你设置得变量,newid+你设置得变量 from table 插入到你得table表 接着delete table表中小于你设置得变量得值 最后update table中得id跟newid得值,让他们减去你前面设得变量值....可能说得比较模糊.. 看下下面得... create table #tb(id int,newid int) insert into #tb(id,newid) values(1,1) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) values(1,1) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) values(3,3) insert into #tb(id,newid) values(3,3) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) values(2,2) insert into #tb(id,newid) select distinct id+1000,newid+1000 from #tbdelete from #tb where id<1000 and newid<1000update #tb set id = id-1000,newid=newid-1000select * from #tbdrop table #tb///这里得1000就是我设置得最大值了...
delete from a where id>0
update a set id=abs(id),newid=abs(newid)select * from a
distinct就是用来筛选重复得数据得..测试如下.
create table #tb(id int,newid int)
insert into #tb(id,newid) values(1,1)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(1,1)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(3,3)
insert into #tb(id,newid) values(3,3)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(2,2)
select distinct * from #tbdrop table #tb
结果:
id newid
1 1
2 2
3 3
id newid
1 1
2 2
3 3
不能用临时表,该如何写SQL语句?
老大,这样怎么不行啦
导到另外一个表
declare @id int
declare record cursor for select id,count(*) from Testtable group by id having count(*) > 1
open record
fetch record into @id,@counts
while @@fetch_status=0
begin
select @counts = @counts -1
set rowcount @counts
delete from Testtable where id = @id
fetch record into @id,@counts
end
close record
deallocate record
set rowcount 0
select * from Testtable order by id
declare @id int
declare record cursor for select id,count(*) from Testtable group by id having count(*) > 1
open record
fetch record into @id,@counts
while @@fetch_status=0
begin
select @counts = @counts -1
set rowcount @counts
delete from Testtable where id = @id
fetch record into @id,@counts
end
close record
deallocate record
set rowcount 0
select * from Testtable order by id
1.游标。
上面有朋友写过了,我写的话也是差不多的,只是细节上略有差别,就不写了。
2.临时表或表变量
declare @t table(id int,newid int)
insert @t select distinct id,newid from tb
truncate table tb
insert tb select id,newid from @t
3,加标识列
create table tb(id int,newid int)
insert into tb(id,newid) values(1,1)
insert into tb(id,newid) values(2,2)
insert into tb(id,newid) values(2,2)
insert into tb(id,newid) values(1,1)
insert into tb(id,newid) values(2,2)
insert into tb(id,newid) values(3,3)
insert into tb(id,newid) values(3,3)
insert into tb(id,newid) values(2,2)
insert into tb(id,newid) values(2,2)alter table tb add idx int identity(1,1)
go
delete a from tb a where exists(select 1 from tb where idx<a.idx and checksum(a.id,a.newid)=checksum(id,newid))
go
alter table tb drop column idx
select * from tb
go
drop table tb
go
支持!!!
能否采用这样得方法,比如你可以先获取到你得id和newid得最大值
然后设置一个变量大于你前面取到得最大值
再用select distinct id+你设置得变量,newid+你设置得变量 from table
插入到你得table表
接着delete table表中小于你设置得变量得值
最后update table中得id跟newid得值,让他们减去你前面设得变量值....可能说得比较模糊..
看下下面得...
create table #tb(id int,newid int)
insert into #tb(id,newid) values(1,1)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(1,1)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(3,3)
insert into #tb(id,newid) values(3,3)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid)
select distinct id+1000,newid+1000 from #tbdelete from #tb where id<1000 and newid<1000update #tb set id = id-1000,newid=newid-1000select * from #tbdrop table #tb///这里得1000就是我设置得最大值了...