在SQL Server2000里面,关于清除重复记录的问题.
ConsumeID   EmployeeID   ConsumeType   ConsumeItem           Dateinfo  
273960               A1                 1号机               午餐                     2007-01-07   11:26:32.000  
273962               A1                 1号机               午餐                     2007-01-07   11:26:32.000  
273994               A2                 1号机               晚餐                     2007-01-07   18:13:02.000  
273995               A2                 1号机               晚餐                     2007-01-07   18:13:02.000  

挑选出Dateinfo相同的记录删除.

------解决方案--------------------
delete from tablename where not exists(select min(ConsumeID) as ConsumeID, EmployeeID, ConsumeType, ConsumeItem , Dateinfo from tablename)
------解决方案--------------------
select identity(int,1,1) as autoID, * into #Tmp from table1

select min(autoID) as autoID
into #Tmp2
from #Tmp
group by Dateinfo

truncate table table1
insert table1 select * from #Tmp where autoID in(select autoID from #tmp2)
------解决方案--------------------
create table tab (ConsumeID varchar(10), EmployeeID varchar(10), ConsumeType varchar(10), ConsumeItem varchar(10),Dateinfo datetime)
insert tab
select '273960 ', 'A1 ', '1号机 ', '午餐 ', '2007-01-07 11:26:32.000 '
union all
select '273962 ', 'A1 ', '1号机 ', '午餐 ', '2007-01-07 11:26:32.000 '
union all
select '273994 ', 'A2 ', '1号机 ', '晚餐 ', '2007-01-07 18:13:02.000 '
union all
select '273995 ', 'A2 ', '1号机 ', '晚餐 ', '2007-01-07 18:13:02.000 '


delete tab from tab a where ConsumeID <(select ConsumeID from tab where Dateinfo=a.Dateinfo and ConsumeID> a.ConsumeID )
------解决方案--------------------
delete T
where ConsumeID not in
(
select min(ConsumeID) from T group by Dateinfo
)
------解决方案--------------------
CREATE TABLE TEST(
ConsumeID INT,EmployeeID NCHAR(2),ConsumeType NCHAR(10),ConsumeItem NCHAR(10),Dateinfo DATETIME)
INSERT TEST
SELECT 273999 , 'A3 ' , '1号机 ' , '晚餐 ' , '2007-01-07 18:13:09.000 ' UNION ALL -- TEST
SELECT 273960 , 'A1 ' , '1号机 ' , '午餐 ' , '2007-01-07 11:26:32.000 ' UNION ALL
SELECT 273962 , 'A1 ' , '1号机 ' , '午餐 ' , '2007-01-07 11:26:32.000 ' UNION ALL
SELECT 273994 , 'A2 ' , '1号机 ' , '晚餐 ' , '2007-01-07 18:13:02.000 ' UNION ALL
SELECT 273995 , 'A2 ' , '1号机 ' , '晚餐 ' , &