select identity(int,1,1) as autoID, * into #Tmp from table1select min(autoID) as autoID into #Tmp2 from #Tmp group by Dateinfotruncate 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 TESTDELETE TEST FROM TEST I WHERE ConsumeID NOT IN(SELECT MIN(ConsumeID) FROM TEST WHERE DATEINFO=I.DATEINFO)SELECT * FROM TESTDROP 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) --结果 /* ConsumeID EmployeeDateinfo ConsumeType ConsumeItem EmployeeDateinfo 273960 A1 1号机 午餐 2007-01-07 11:26:32.000 273994 A2 1号机 晚餐 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 )
into #Tmp2
from #Tmp
group by Dateinfotruncate table table1
insert table1 select * from #Tmp where autoID in(select autoID from #tmp2)
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 )
where ConsumeID not in
(
select min(ConsumeID) from T group by Dateinfo
)
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 TESTDELETE TEST FROM TEST I WHERE ConsumeID NOT IN(SELECT MIN(ConsumeID) FROM TEST WHERE DATEINFO=I.DATEINFO)SELECT * FROM TESTDROP TABLE TEST
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) --结果
/*
ConsumeID EmployeeDateinfo ConsumeType ConsumeItem EmployeeDateinfo
273960 A1 1号机 午餐 2007-01-07 11:26:32.000
273994 A2 1号机 晚餐 2007-01-07 18:13:02.000
*/
where ConsumeID not in
(
select max(ConsumeID) from T group by Dateinfo
)