34,594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @TB TABLE([time] DATETIME, [data1] DECIMAL(10,2))
INSERT @TB
SELECT '2009-9-13 13:00:00', 1.5 UNION ALL
SELECT '2009-9-13 13:05:00', 2.5 UNION ALL
SELECT '2009-9-13 13:10:00', 3.5 UNION ALL
SELECT '2009-9-13 13:15:00', 2.5 UNION ALL
SELECT '2009-9-13 13:20:00', 4.5 UNION ALL
SELECT '2009-9-13 13:25:00', 5.5 UNION ALL
SELECT '2009-9-13 13:30:00', 6.5 UNION ALL
SELECT '2009-9-13 13:35:00', 5.5 UNION ALL
SELECT '2009-9-13 13:40:00', 5.5
delete @TB
from @TB a
where data1<>(select max(data1) from @TB b where a.time>=b.time)
select * from @TB
DELETE TB FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE TIME <T.TIME AND DATA1>T.DATA1)
delete tb from tb t where exists(select 1 from tb where time>t.time and data1<t.data1)
DELETE TB FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE TIME<T.TIME AND DATA1>T.DATA1)
DECLARE @TB TABLE([time] DATETIME, [data1] DECIMAL(10,2))
INSERT @TB
SELECT '2009-9-13 13:00:00', 1.5 UNION ALL
SELECT '2009-9-13 13:05:00', 2.5 UNION ALL
SELECT '2009-9-13 13:10:00', 3.5 UNION ALL
SELECT '2009-9-13 13:15:00', 2.5 UNION ALL
SELECT '2009-9-13 13:20:00', 4.5 UNION ALL
SELECT '2009-9-13 13:25:00', 5.5 UNION ALL
SELECT '2009-9-13 13:30:00', 6.5 UNION ALL
SELECT '2009-9-13 13:35:00', 5.5 UNION ALL
SELECT '2009-9-13 13:40:00', 5.5
DELETE T
FROM @TB AS T
WHERE EXISTS(SELECT * FROM @TB WHERE TIME<T.TIME AND DATA1>T.DATA1)
SELECT *
FROM @TB
/*
time data1
------------------------------------------------------ ------------
2009-09-13 13:00:00.000 1.50
2009-09-13 13:05:00.000 2.50
2009-09-13 13:10:00.000 3.50
2009-09-13 13:20:00.000 4.50
2009-09-13 13:25:00.000 5.50
2009-09-13 13:30:00.000 6.50
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-14 14:06:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([time] datetime,[data1] numeric(2,1))
insert [tb]
select '2009-9-13 13:00:00',1.5 union all
select '2009-9-13 13:05:00',2.5 union all
select '2009-9-13 13:10:00',3.5 union all
select '2009-9-13 13:15:00',2.5 union all
select '2009-9-13 13:20:00',4.5 union all
select '2009-9-13 13:25:00',5.5 union all
select '2009-9-13 13:30:00',6.5 union all
select '2009-9-13 13:35:00',5.5 union all
select '2009-9-13 13:40:00',5.5
--------------开始查询--------------------------
DELETE TB FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE TIME<T.TIME AND DATA1>T.DATA1)
select * from tb
----------------结果----------------------------
/*time data1
----------------------- ---------------------------------------
2009-09-13 13:00:00.000 1.5
2009-09-13 13:05:00.000 2.5
2009-09-13 13:10:00.000 3.5
2009-09-13 13:20:00.000 4.5
2009-09-13 13:25:00.000 5.5
2009-09-13 13:30:00.000 6.5
(6 行受影响)
*/
DELETE TB FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE TIME<T.TIME AND DATA1>T.DATA1)
?