create table #temptable
(
Id int primary key,
xx nvarchar(10),
yy datetime,
zz int,
aa int
)
当xx、yy、zz、aa四个字段相同时，只保留Id最小的一条
例如原来的表中数据
1 haha 2015-01-01 5 0
2 hahaha 2015-02-02 7 0
3 haha 2015-01-01 5 0
4 hahaha 2015-02-02 7 0
5 ha 2015-02-02 7 0
要删掉Id为3,4的，保留1,2,5
------解决思路----------------------
DELETE #temptable
WHERE EXISTS (
SELECT *
FROM #temptable t
WHERE t.id < #temptable.id
AND t.xx = #temptable.xx
AND t.yy = #temptable.yy
AND t.zz = #temptable.zz
AND t.aa = #temptable.aa
)
SELECT * FROM #temptable
Id xx yy zz aa
----------- ---------- ---------- ----------- -----------
1 haha 2015-01-01 5 0
2 hahaha 2015-02-02 7 0
5 ha 2015-02-02 7 0
------解决思路----------------------
with cte(id,xx,yy,zz,aa)
as
(
select 1,'haha','2015-01-01',5,0 union all
select 2,'hahaha','2015-02-02',7,0 union all
select 3,'haha','2015-01-01',5,0 union all
select 4,'hahaha','2015-02-02',7,0 union all
select 5,'ha','2015-02-02',7,0
)
select min(id) as id,xx,yy,zz,aa from cte group by xx,yy,zz,aa
order by id