如何计算几个时间段时间的总和

[yixueweima]

现在有一张表中，记录了几个时间段，开始时间和结束时间。我想求得这几个时间段的时间总和，去掉交集，并且不要计算空挡，请问大家要怎么做呢

[唐诗三百首]

请提供表结构,测试数据和希望结果, 才能帮你写语句喔.

[中国风]

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 )

[yixueweima]

-----建表 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') 想要求的结果，就是这几个时间加起来一共的天数，有交集的地方只求一次 。

[yixueweima]

引用 2 楼 yixueweima 的回复:

-----建表 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') 想要求的结果，就是这几个时间加起来一共的天数，有交集的地方只求一次 。

结果应该是1月1到4月1和5月1到6月1两个时间段天数的和

[永爱果果]

学习了啊，楼上的正确

[yixueweima]

引用 5 楼 roy_88 的回复:

这样?

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 )

这个统计的是1月1到2月1加上1月1到4月1的时间吧

[C_B_Lu]

引用 5 楼 roy_88 的回复:

这样?

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 )