原表如下:
id address phone
33 shenzhen 86023747
36 guangzhou 82536565
33 shenzhen 86023747
33 shenzhen 86023747
36 guangzhou 82536565
33 shenzhen 86023747
38 dalian 26835692
新表如下:
33 shenzhen 86023747
36 guangzhou 82536565
38 dalian 26835692
id address phone
33 shenzhen 86023747
36 guangzhou 82536565
33 shenzhen 86023747
33 shenzhen 86023747
36 guangzhou 82536565
33 shenzhen 86023747
38 dalian 26835692
新表如下:
33 shenzhen 86023747
36 guangzhou 82536565
38 dalian 26835692
drop table 原来的表
信息大致意思为不能对text,ntext,image数据进行比较
drop oldtable没经过测试~~~
我测了,新表数据仍然重复
drop table oldtable
go
if exists(select * from sysobjects where id=object_id('newtable') and xtype='u')
drop table newtable
go
create table oldtable([id] int,address varchar(20),phone varchar(20))
insert into oldtable select 33,'shenzhen','86023747'
union all select 36,'guangzhou','82536565'
union all select 33,'shenzhen','86023747'
union all select 33,'shenzhen','86023747'
union all select 36,'guangzhou','82536565'
union all select 33,'shenzhen','86023747'
union all select 38,'dalian','26835692'
go
create table newtable([id] int,address varchar(20),phone varchar(20))
go
insert into newtable select distinct * from oldtable
drop table oldtable
select * from newtable
然后删除oldtable
group by col1 , col2 ,col3
drop table t
SELECT NumID = IDENTITY(int, 1, 1),*
INTO #NewTable
FROM OldTableSELECT MAX(NumID) AS NumID,id
INTO #SAVEIDTABLE
FROM NewTable
GROUP BY idDELETE FROM #NewTable
WHERE NumID NOT IN (SELECT NumID FROM #SAVEIDTABLE)SELECT * FROM #NewTableDROP TABLE #NewTable
DROP TABLE #SAVEIDTABLE
--OVER--是这个意思么?
select address , phone from table group by address , phone