SQL SERVER 去掉重复记录
今天应一个网友的要求说帮他写一个SQL 语句条件是：如果出现DataID,TableName出现多次只取第一条记录
if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table .
GO
CREATE TABLE . (
IDENTITY (1, 1) NOT NULL ,
NOT NULL ,
(50) COLLATE Chinese_PRC_CI_AS NULL ,
NULL ,
(100) COLLATE Chinese_PRC_CI_AS NULL ,
NULL ,
(100) COLLATE Chinese_PRC_CI_AS NULL ,
(100) COLLATE Chinese_PRC_CI_AS NULL ,
(1000) COLLATE Chinese_PRC_CI_AS NULL
) ON
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
页:
[1]