求教一条SQL语句

[game438951]

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

[Tiger_Zhao]

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

[Neo_whl]

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

[yooq_csdn]

如果id是唯一的

delete  #temptable  where id in (select min(id) as id,xx,yy,zz,aa from #temptable group by xx,yy,zz,aa)