在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号机 ' , '晚餐 ' , '2007-01-07 18:13:02.000 '

SELECT * FROM TEST

DELETE TEST FROM TEST I WHERE ConsumeID NOT IN(SELECT MIN(ConsumeID) FROM TEST WHERE DATEINFO=I.DATEINFO)

SELECT * FROM TEST

DROP TABLE TEST
[解决办法]
--如果EmployeeID ConsumeType ConsumeItem不重复呢
declare @t table (ConsumeID varchar(20),EmployeeDateinfo varchar(20),
ConsumeType varchar(20), ConsumeItem varchar(20), Dateinfo datetime)
insert into @t select '273960 ', 'A1 ', '1号机 ', '午餐 ', '2007-01-07 11:26:32.000 '
insert into @t select '273962 ', 'A1 ', '1号机 ', '午餐 ', '2007-01-07 11:26:32.000 '
insert into @t select '273994 ', 'A2 ', '1号机 ', '晚餐 ', '2007-01-07 18:13:02.000 '
insert into @t select '273995 ', 'A2 ', '1号机 ', '晚餐 ', '2007-01-07 18:13:02.000 '

--方法一
select * from @t a
where not exists
(select 1 from @t b where b.ConsumeID <a.ConsumeID
and b.EmployeeDateinfo=a.EmployeeDateinfo and b.ConsumeType=a.ConsumeType
and b.ConsumeItem=a.ConsumeItem and b.Dateinfo=a.Dateinfo)

--方法二
select * from @t
where ConsumeID in
(select min(ConsumeID) from @t group by EmployeeDateinfo,ConsumeType,ConsumeItem,EmployeeDateinfo)

--结果
/*
ConsumeIDEmployeeDateinfoConsumeTypeConsumeItemEmployeeDateinfo
273960A11号机午餐2007-01-07 11:26:32.000
273994A21号机晚餐2007-01-07 18:13:02.000
*/
[解决办法]
delete T
where ConsumeID not in
(
select max(ConsumeID) from T group by Dateinfo
)
[解决办法]
delete tabname from tabname as a where ConsumeID <(select ConsumeID from tab where Dateinfo=a.Dateinfo and ConsumeID> a.ConsumeID )