name    Item    value    tag
John    ITCH    89.33    1
John    ITCH    33.22    2
Alex    ITCH    67.77    3
Jacky    ITCH    89.01    1
Alex    YHTC    34.22    2
Bruce    YHTC    35.11    2
Bruce    YHTC    89.11    3
...

--功能概述:删除重复记录 
  
 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢!  
 1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断  
 select * from people  
 where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)  
  
 2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录  
 delete from people  
 where peopleId  in (select  peopleId  from people  group  by  peopleId  having  count(peopleId) > 1)  
 and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)  
  
 3、查找表中多余的重复记录（多个字段）  
 select * from vitae a  
 where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)  
  
 4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录  
 delete from vitae a  
 where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)  
 and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)  
  
 5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录  
 select * from vitae a  
 where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)  
 and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)  
  
 比方说在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，  
 现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；  
 Select Name,Count(*) From A Group By Name Having Count(*) > 1  
  
 如果还查性别也相同大则如下:  
 Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

select * from tb t 
where not exists(select * from tb where t.name=name and t.item=item and tag<t.tag )

------解决方案--------------------
SQL code
delete a 
from ta a 
where tag in (select min(tag)  from ta where name=a.name amd Item = a.Item  )

------解决方案--------------------
SQL code
delete a 
from ta a 
where tag <> (select min(tag)  from ta where name=a.name amd Item = a.Item  )

------解决方案--------------------
SQL code
---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-07-15 07:06:42
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (name VARCHAR(5),Item VARCHAR(4),value NUMERIC(4,2),tag INT)
INSERT INTO [tb]
SELECT 'John','ITCH',89.33,1 UNION ALL
SELECT 'John','ITCH',33.22,2 UNION ALL
SELECT 'Alex','ITCH',67.77,3 UNION ALL
SELECT 'Jacky','ITCH',89.01,1 UNION ALL
SELECT 'Alex','YHTC',34.22,2 UNION ALL
SELECT 'Bruce','YHTC',35.11,2 UNION ALL
SELECT 'Bruce','YHTC',89.11,3



delete t
from tb t where  exists(select 1 from t