SQL SERVER 去掉重复记录

今天应一个网友的要求说帮他写一个SQL 语句

条件是：如果出现DataID,TableName出现多次只取第一条记录

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Votes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Votes]
GO

CREATE TABLE [dbo].[Votes] (
 [ID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [CompanyID] [bigint] NOT NULL ,
 [TableName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [DataID] [bigint] NULL ,
 [VoteUser] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [VoteTime] [datetime] NULL ,
 [VoteIP] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [VoteType] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [VoteInfo] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

我的写法:

select a.* from Votes a inner join (select min(ID) as ID from Votes group by DataID,TableName having (count(*)>1)) b on a.ID =b.ID
union all
select a.* from Votes a inner join (select min(ID) as ID from Votes group by DataID,TableName having (count(*)=1)) b on a.ID =b.ID

 

优化一下：

 

select a.* from Votes a inner join (select min(ID) as ID from Votes group by DataID,TableName ) b on a.ID =b.ID