34,590
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t([时间] DATETIME,[1Q] int,[2Q] INT,[3Q] INT)
INSERT INTO t VALUES ('2017-01-01 1:00:00',12,16,44)
INSERT INTO t VALUES ('2017-01-01 1:10:00',13,17,45)
INSERT INTO t VALUES ('2017-01-01 1:20:00',14,18,46)
INSERT INTO t VALUES ('2017-01-01 1:30:00',15,19,47)
INSERT INTO t VALUES ('2017-01-01 1:40:00',16,20,48)
INSERT INTO t VALUES ('2017-01-01 1:50:00',17,21,49)
INSERT INTO t VALUES ('2017-01-01 2:00:00',18,22,50)
UPDATE t SET [1Q]=NULL,[2Q]=NULL
WHERE datepart(minute,[时间])!=0
OR
datepart(second,[时间])!=0
SELECT * FROM t
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([时间] DATETIME,[1Q] int,[2Q] int,[3Q] int)
Insert #T
select N'2017/1/1 1:00:00',12,16,44 union all
select N'2017/1/1 1:10:00',13,17,45 union all
select N'2017/1/1 1:20:00',14,18,46 union all
select N'2017/1/1 1:30:00',15,19,47 union all
select N'2017/1/1 1:40:00',16,20,48 union all
select N'2017/1/1 1:50:00',17,21,49 union all
select N'2017/1/1 2:00:00',18,22,50
Go
--测试数据结束
UPDATE #T
SET [1Q] = NULL ,
[2Q] = NULL
WHERE 时间 NOT IN ( SELECT 时间
FROM #T
WHERE DATEDIFF(MINUTE, 时间, '2017/1/1 00:00:00') % 60 = 0 )
SELECT * FROM #T