SELECT * from users order by id
/*新建一个表users*/
CREATE TABLE users
(
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[sex] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
/*插入数据*/
insert into users ([name],sex) values('bbb','nv')
insert into users ([name],sex) values('aaa','nan')
insert into users ([name],sex) values('aaa','nv')
insert into users ([name],sex) values('bbb','nv')
/*全部的字段都一样的重复*/
select distinct * into #Tmp from users
drop table users
select * into users from #Tmp
drop table #Tmp
/*只有个别的字段重复 下面的只有name,sex两个重复*/
select * from users
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Tmp1')) drop table #Tmp1
select identity(int,1,1) as autoid, * into #Tmp1 from users
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Tmp2')) drop table #Tmp2
select min(autoid) as autoid into #Tmp2 from #Tmp1 group by [name],sex
if exists(select * from dbo.sysobjects where id = object_id(N'users') and OBJECTPROPERTY(id,N'IsUserTable') = 1) drop table users
select id,[name],sex into users from #Tmp1 where autoid in(select autoid from #tmp2)
/*新建一个表users*/
CREATE TABLE users
(
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[sex] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
/*插入数据*/
insert into users ([name],sex) values('bbb','nv')
insert into users ([name],sex) values('aaa','nan')
insert into users ([name],sex) values('aaa','nv')
insert into users ([name],sex) values('bbb','nv')
/*全部的字段都一样的重复*/
select distinct * into #Tmp from users
drop table users
select * into users from #Tmp
drop table #Tmp
/*只有个别的字段重复 下面的只有name,sex两个重复*/
select * from users
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Tmp1')) drop table #Tmp1
select identity(int,1,1) as autoid, * into #Tmp1 from users
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Tmp2')) drop table #Tmp2
select min(autoid) as autoid into #Tmp2 from #Tmp1 group by [name],sex
if exists(select * from dbo.sysobjects where id = object_id(N'users') and OBJECTPROPERTY(id,N'IsUserTable') = 1) drop table users
select id,[name],sex into users from #Tmp1 where autoid in(select autoid from #tmp2)