关于SQL删除重复记录的问题.谢谢!
数据如下:
ID Time Price
A01 2006-11-10 12:10:48.000 3.5
A01 2006-11-10 12:11:00.000 3.5
A02 2006-12-01 08:01:00.000 3.5
A02 2006-12-01 08:03:00.000 3.5
问题:需要把相差2分钟之内的数据只取其中一条.谢谢!
[解决办法]
DECLARE @T TABLE
(
ID VARCHAR(20),
[Time Price] DATETIME
)
INSERT INTO @T
SELECT 'A01 ', '2006-11-10 12:10:48.000 '
UNION ALL
SELECT 'A01 ', '2006-11-10 12:11:00.000 '
UNION ALL
SELECT 'A02 ', '2006-12-01 08:01:00.000 '
UNION ALL
SELECT 'A02 ', '2006-12-01 08:03:00.000 '
SELECT * FROM @T AS T
WHERE EXISTS
(SELECT 1 FROM @T
WHERE T.ID = ID
AND (ABS(DATEDIFF(MINUTE,[Time Price],T.[Time Price])) > = 2
OR (DATEDIFF(MINUTE,[Time Price],T.[Time Price]) > 0
AND DATEDIFF(MINUTE,[Time Price],T.[Time Price]) < 2))
)
刚才没复制完整。补上
