34,591
社区成员
发帖
与我相关
我的任务
分享
declare @t table
(
Code char(6),
CreateTime datetime,
Price float
)
insert @t select '031002','2008-10-17 15:00:15',4.558
union all select '031002','2008-10-17 15:00:16',4.554
union all select '031002','2008-10-17 15:00:17',4.554
union all select '031002','2008-10-17 15:00:18',4.561
union all select '031003','2008-10-17 15:00:19',4.562
union all select '031003','2008-10-17 15:00:20',4.562
union all select '031003','2008-10-17 15:00:21',4.562
union all select '031004','2008-10-17 15:00:22',4.565
union all select '031004','2008-10-17 15:00:23',4.565
union all select '031004','2008-10-17 15:00:24',4.567
delete a
from @t a
where exists(
select *
from @t
where Code = a.Code
and Price = a.Price
and CreateTime > a.CreateTime)
select * from @t
--设表名为t
delete a from t as a where exists(select * from t where code=a.code and price=a.price and createtime>a.createtime)
insert @t select '031002','2008-10-17 15:00:15',4.558
union all select '031002','2008-10-17 15:00:15',4.558
union all select '031002','2008-10-17 15:00:15',4.558
union all select '031002','2008-10-17 15:00:15',4.558
union all select '031003','2008-10-17 15:00:19',4.562
union all select '031003','2008-10-17 15:00:19',4.562
union all select '031003','2008-10-17 15:00:19',4.562
union all select '031004','2008-10-17 15:00:22',4.565
union all select '031004','2008-10-17 15:00:22',4.565
union all select '031004','2008-10-17 15:00:22',4.565
declare @t table
(
Code char(6),
CreateTime datetime,
Price float
)
insert @t select '031002','2008-10-17 15:00:15',4.558
union all select '031002','2008-10-17 15:00:16',4.554
union all select '031002','2008-10-17 15:00:17',4.554
union all select '031002','2008-10-17 15:00:18',4.561
union all select '031003','2008-10-17 15:00:19',4.562
union all select '031003','2008-10-17 15:00:20',4.562
union all select '031003','2008-10-17 15:00:21',4.562
union all select '031004','2008-10-17 15:00:22',4.565
union all select '031004','2008-10-17 15:00:22',4.565
union all select '031004','2008-10-17 15:00:22',4.565
delete a
from (
select
row_num = row_number() over(partition by Code,Price order by CreateTime desc)
from @t
)a
where row_num > 1
select * from @t
/**
031002 2008-10-17 15:00:15.000 4.558
031002 2008-10-17 15:00:17.000 4.554
031002 2008-10-17 15:00:18.000 4.561
031003 2008-10-17 15:00:21.000 4.562
031004 2008-10-17 15:00:22.000 4.565
**/
declare @t table
(
Code char(6),
CreateTime datetime,
Price float
)
insert @t select '031002','2008-10-17 15:00:15',4.558
union all select '031002','2008-10-17 15:00:15',4.558
union all select '031002','2008-10-17 15:00:15',4.558
union all select '031002','2008-10-17 15:00:15',4.558
union all select '031003','2008-10-17 15:00:19',4.562
union all select '031003','2008-10-17 15:00:19',4.562
union all select '031003','2008-10-17 15:00:19',4.562
union all select '031004','2008-10-17 15:00:22',4.565
union all select '031004','2008-10-17 15:00:22',4.565
union all select '031004','2008-10-17 15:00:22',4.565
/**
希望的结果
031002 2008-10-17 15:00:15.000 4.558
031003 2008-10-17 15:00:19.000 4.562
031004 2008-10-17 15:00:22.000 4.565
**/
delete a
from tb a
where exists(select * from @t
where Code = a.Code and Price = a.Price and CreateTime > a.CreateTime)
delete a from @t where createTime<any(select createTime from @t where code=a.code and price=a.price)