szy_temp里面有重复的数据insert into szy_max
select gdsid,kqbh,gys,max(lastmodified)
from szy_temp
group by gdsid,kqbh,gys 查询1:delete szy_temp
from szy_temp a,szy_max b
where a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and a.lastmodified < b.lastmodified select * from szy_temp
查询2: select *
from szy_temp a,szy_max b
where a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and a.lastmodified = b.lastmodified
--szy_temp里面有重复的数据declare @szy_temp table(gdsid int,kqbh int,gys int,lastmodified datetime)
declare @szy_max table(gdsid int,kqbh int,gys int,lastmodified datetime)insert into @szy_temp
values(1,1,1,'2013-01-01 00:00:01'),
(1,1,1,'2013-01-01 00:00:05')
insert into @szy_max
select gdsid,kqbh,gys,max(lastmodified)
from @szy_temp
group by gdsid,kqbh,gys --查询1:delete @szy_temp
from @szy_temp a,@szy_max b
where a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and a.lastmodified < b.lastmodified select * from @szy_temp
--查询2:select a.*
from @szy_temp a,@szy_max b
where a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and a.lastmodified = b.lastmodified
--查询1:
SELECT *
from szy_temp a
INNER JOIN
(
select gdsid,kqbh,gys,lastmodified=max(lastmodified)
from szy_temp
group by gdsid,kqbh,gys
) b
on a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and a.lastmodified < b.lastmodified
--查询2:
select *
from szy_temp a
INNER JOIN
(
select gdsid,kqbh,gys,lastmodified=max(lastmodified)
from szy_temp
group by gdsid,kqbh,gys
) b
on a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and a.lastmodified = b.lastmodified --查询3:再试试这个SQL,看看结果如何?
SELECT * FROM szy_temp a
WHERE NOT EXISTS
(
SELECT 1
FROM szy_temp b
WHERE a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and b.lastmodified > a.lastmodified
)
--查询1:--一共有多少条记录
select COUNT(*)
from szy_temp
--计算lastmodified小于最大值的记录数,也就是要删除多少条记录
select COUNT(*)
from szy_temp a,szy_max b
where a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and a.lastmodified < b.lastmodified --查询2:--计算lastmodified等于最大值的记录数,也就是删除后剩余的记录数
select COUNT(*)
from szy_temp a,szy_max b
where a.gdsid = b.gdsid and a.kqbh = b.kqbh and a.gys = b.gys and a.lastmodified = b.lastmodified