增加一个自动递增ID
delete a.* from tl a ,tl b where a.id>b.id and a.单据编号=b.单据编号 and a.人员编号=b.人员编号 and a.填报数=b.填报数 and a.实报数=b.实报数 and a.填报时间=b.填报时间
delete a.* from tl a ,tl b where a.id>b.id and a.单据编号=b.单据编号 and a.人员编号=b.人员编号 and a.填报数=b.填报数 and a.实报数=b.实报数 and a.填报时间=b.填报时间
0099 A0024 5.5 5 2002-02-23 <---
0099 A0012 32.5 30 2002-02-23好像没有重复啊?
编号相同的一组就是重复了的。单据编号 人员编号 填报数 实报数 填报时间
0021 A0012 52.5 50 2002-02-23 <---1
0021 A0233 57.5 45 2002-02-23
0065 A0024 5.5 5 2002-05-23 <---2
0035 A0012 52.5 50 2002-02-23
0065 A0055 32.9 32 2002-05-23
0099 A0012 52.5 50 2002-02-23 <---1
0099 A0024 5.5 5 2002-02-23 <---2
0021 A0052 65 65 2002-02-23
0099 A0012 32.5 30 2002-02-23
WHERE 单据编号 <> (
SELECT TOP 1 单据编号
FROM t1 x
WHERE t1.人员编号 = x.人员编号, t1.填报数 = x.填报数,
t1.实报数 = x.实报数, t1.填报时间 = x.填报时间
ORDER BY 单据编号
) AND 单据编号 IN (
SELECT 单据编号
FROM t1 y
WHERE t1.人员编号 = y.人员编号, t1.填报数 = y.填报数,
t1.实报数 = y.实报数, t1.填报时间 = y.填报时间
)
insert t1 values('0021','A0012',52.5,50,'2002-02-23')
insert t1 values('0099','A0012',52.5,50,'2002-02-23')
insert t1 values('0021','A0233',57.5 , 45 ,'2002-02-23')
insert t1 values('0065','A0024',5.5,5 ,'2002-05-23')
insert t1 values('0035','A0012',52.5 , 50 ,'2002-02-23')
insert t1 values('0065','A0055',32.9 , 32 ,'2002-05-23')
insert t1 values('0099','A0024',5.5,5,'2002-02-23')
insert t1 values('0021','A0052', 65 , 65 ,'2002-02-23')
insert t1 values('0099','A0012',32.5 , 30 ,'2002-02-23')
delete t1 where not exists (select 1 from t1 tem group by tem.人员编号,tem.填报数,tem.实报数 having tem.人员编号=t1.人员编号 and tem.填报数=t1.填报数 and tem.实报数=t1.实报数 and min(tem.单据编号)=t1.单据编号)
select * from t1
go
drop table t1