-----建表 CREATE TABLE TestDateLong ( ID VARCHAR(50) PRIMARY KEY, BeginDate DATETIME, EndDate DATETIME ) ----测试数据 INSERT INTO TestDateLong VALUES (NEWID(),'2015-01-01','2015-02-01') INSERT INTO TestDateLong VALUES (NEWID(),'2015-02-01','2015-03-01') INSERT INTO TestDateLong VALUES (NEWID(),'2015-05-01','2015-06-01') INSERT INTO TestDateLong VALUES (NEWID(),'2015-01-01','2015-04-01')想要求的结果,就是这几个时间加起来一共的天数,有交集的地方只求一次 。
SELECT SUM(DATEDIFF(dd,BeginDate,EndDate)) FROM TestDateLong--计算当天时+1 SELECT SUM(DATEDIFF(dd,BeginDate,EndDate)+1) FROM TestDateLong
这样?SELECT DATEDIFF(dd,BeginDate,EndDate) AS [天数] FROM TestDateLong AS a WHERE NOT EXISTS(SELECT 1 FROM TestDateLong WHERE BeginDate<=a.BeginDate AND EndDate<=a.BeginDate AND ID<>a.ID )--计算当天时+1 SELECT DATEDIFF(dd,BeginDate,EndDate)+1 AS [天数] FROM TestDateLong AS a WHERE NOT EXISTS(SELECT 1 FROM TestDateLong WHERE BeginDate<=a.BeginDate AND EndDate<=a.BeginDate AND ID<>a.ID )
CREATE TABLE TestDateLong (
ID VARCHAR(50) PRIMARY KEY,
BeginDate DATETIME,
EndDate DATETIME
)
----测试数据
INSERT INTO TestDateLong VALUES (NEWID(),'2015-01-01','2015-02-01')
INSERT INTO TestDateLong VALUES (NEWID(),'2015-02-01','2015-03-01')
INSERT INTO TestDateLong VALUES (NEWID(),'2015-05-01','2015-06-01')
INSERT INTO TestDateLong VALUES (NEWID(),'2015-01-01','2015-04-01')想要求的结果,就是这几个时间加起来一共的天数,有交集的地方只求一次 。
SELECT SUM(DATEDIFF(dd,BeginDate,EndDate)+1) FROM TestDateLong
WHERE NOT EXISTS(SELECT 1 FROM TestDateLong WHERE BeginDate<=a.BeginDate AND EndDate<=a.BeginDate AND ID<>a.ID )--计算当天时+1
SELECT DATEDIFF(dd,BeginDate,EndDate)+1 AS [天数] FROM TestDateLong AS a
WHERE NOT EXISTS(SELECT 1 FROM TestDateLong WHERE BeginDate<=a.BeginDate AND EndDate<=a.BeginDate AND ID<>a.ID )