时间段去重复求和

Question

 --sql2012
CREATE TABLE times(
    [a] [datetime],
    [b] [datetime]
    )
--下面的数据去重复求和是3小时。我怎么能查出3来？
insert into times(a,b) values('2010-01-01 9:00:00','2010-01-01 10:00:00')
insert into times(a,b) values('2010-01-01 8:00:00','2010-01-01 9:30:00')
insert into times(a,b) values('2010-01-01 11:00:00','2010-01-01 12:00:00')
  --method one
 with a as(
 select min(a) as a,max(b) as b from times
 )
 select a,b,datediff(hour,a,b) as '和' from a
 --结果展示（去重复后求和是4小时）
 /*
 a                       b                       和
----------------------- ----------------------- -----------
2010-01-01 08:00:00.000 2010-01-01 12:00:00.000 4(1 行受影响) */
 --method two select sum(datediff(hour,a,b))as '和'
from times 
--结果展示
/*
和
-----------
3(1 行受影响)
*//*
楼主没有说明，什么去重复求和，所以无法给出肯定的答案，仅供参考
*/

Answer 1

感谢回复，但是这两个方法都不行啊，我要的结果单位是秒的话就是10800，第一个方法把空余时间也算进去了，第二个方法把第一行与第二行有重复的半小时也算进去了select sum(datediff(second,a,b)) as '和' from time
--结果是12600

Answer 2

 --sql2012 truncate table times
CREATE TABLE times(
    [a] [datetime],
    [b] [datetime]
    )
--下面的数据去重复求和是3小时。我怎么能查出3来？
insert into times(a,b) values('2010-01-01 9:00:00','2010-01-01 10:00:00')
insert into times(a,b) values('2010-01-01 8:00:00','2010-01-01 9:30:00')
insert into times(a,b) values('2010-01-01 11:00:00','2010-01-01 12:00:00')
 
select * from times order by a--结果展示
/*
a                       b
----------------------- -----------------------
2010-01-01 08:00:00.000 2010-01-01 09:30:00.000
2010-01-01 09:00:00.000 2010-01-01 10:00:00.000
2010-01-01 11:00:00.000 2010-01-01 12:00:00.000(3 行受影响)*/
--我看过的书都说，top100+orderby，是没用的，今天你打破了这个说法
with a as (
select top (100)percent a,b,lead(a) over(order by a) as lead_a,lag(a) over(order by a) as lag_a,lag(b) over(order by a) as lag_b,lead(b) over(order by a) as lead_b
           from times
   order by a,b
   )
  --这里逻辑众多，需要你独自完善，我写的这个是省略的
   select sum(case when lead_a is not null  and  b >lead_a then datediff(second,a,lead_a) 
               when lead_a is not null  and   b<lead_a  and b not between lag_a and lag_b then datediff(second,a,b) 
   when lead_a is null and a>lag_a then datediff(second,a,b)
   end)
   from a 
   /*
-----------
10800(1 行受影响)
*/

Answer 3

--sql2012 truncate table times
CREATE TABLE times(
    [a] [datetime],
    [b] [datetime]
    )
--下面的数据去重复求和是3小时。我怎么能查出3来？
insert into times(a,b) values('2010-01-01 9:00:00','2010-01-01 10:00:00')
insert into times(a,b) values('2010-01-01 8:00:00','2010-01-01 9:30:00')
insert into times(a,b) values('2010-01-01 11:00:00','2010-01-01 12:00:00')
insert into times(a,b) values('2010-01-01 11:00:00','2010-01-01 12:00:00')  
insert into times(a,b) values('2010-01-01 08:00:00','2010-01-01 11:00:00')  
insert into times(a,b) values('2010-01-01 07:00:00','2010-01-01 11:00:00')  
insert into times(a,b) values('2010-01-01 07:00:00','2010-01-01 9:00:00')  
insert into times(a,b) values('2010-01-01 10:00:00','2010-01-01 12:00:00')  
insert into times(a,b) values('2010-01-01 09:00:00','2010-01-01 12:00:00')  select * from times order by a
 
--结果展示
/*
a                       b
----------------------- -----------------------
2010-01-01 07:00:00.000 2010-01-01 11:00:00.000
2010-01-01 07:00:00.000 2010-01-01 09:00:00.000
2010-01-01 08:00:00.000 2010-01-01 11:00:00.000
2010-01-01 08:00:00.000 2010-01-01 09:30:00.000
2010-01-01 09:00:00.000 2010-01-01 10:00:00.000
2010-01-01 09:00:00.000 2010-01-01 12:00:00.000
2010-01-01 10:00:00.000 2010-01-01 12:00:00.000
2010-01-01 11:00:00.000 2010-01-01 12:00:00.000
2010-01-01 11:00:00.000 2010-01-01 12:00:00.000
2010-01-01 11:00:00.000 2010-01-01 11:00:00.000
2010-01-01 16:00:00.000 2010-01-01 20:00:00.000(11 行受影响)*/
 WITH A AS --过滤掉相同的列
 (
  SELECT DISTINCT A,B FROM TIMES 
 ),
  B AS  --排序，同时筛选出来跨度过长的列，并排除a=b,a>b的列
(SELECT A,B ,ROW_NUMBER() OVER(ORDER BY A,B) AS RN,DATEDIFF(SECOND,A,B) AS diff FROM A where b>a 
),
C as -- 筛选出跨度最大的行
(
select top 1 a,b,diff,rn from B
order by diff desc,a asc,b asc
),
D as (
select b.a as ba,b.b as bb,b.diff as b_diff,c.a as max_a,c.b as max_b,c.diff as max_diff from b 
left outer join C on ((c.rn<=b.rn and b.b>=c.b ) or (c.rn>b.rn and b.a <c.a)) and b.a>=c.a --and  c.diff<=b.diff--过滤掉部分完全重复的行
where c.a is not null and c.b is not null
),
F as(
select ba as a ,bb as b,b_diff,row_number() over(order by ba,bb) as rn 
from D  where (max_a>=ba and max_b<=bb) or (ba>max_a and bb>max_b) --完全过滤掉重复行
)
select sum(case when f1.a is not null and f2.a is null and f.b>f1.a then f.b_diff
            when f1.a is not null and f2.a is null and f.b<=f1.a then f.b_diff
            when f1.a is not null and f2.a is not null and f.a <f2.b and f.a>f2.a then datediff(second,f2.b,f.b)
            when f1.a is not null and f2.a is not null and f.a>f2.a and f.b<=f2.b then 0
when f1.a is not null and f2.a is not null and f.a>f2.b  then f.b_diff
when f1.a is null and f2.a is not null and  f.a>f2.b then f.b_diff
else 0 end )
from f left outer join f as f1 on f1.rn-1 =f.rn 
       left outer join f as f2 on f2.rn+1 =f.rn    where f2.a<f.a--结果展示（注：该语句仅为sql2005以及以上版本可用，但是如果你是sql2012可以更精简，这个）
   /*
   
   
-----------
18000(1 行受影响)    */

Answer 4

把where去掉就可以，当时，我为什么加where来着，我忘记了

Answer 5

 把最后一行， where f2.a<f.a ，注释掉

Answer 6

---新方法
select * from times 
/*
a                       b
----------------------- -----------------------
2010-01-01 09:00:00.000 2010-01-01 10:00:00.000
2010-01-01 08:00:00.000 2010-01-01 09:30:00.000
2010-01-01 11:00:00.000 2010-01-01 12:00:00.000
2010-01-01 11:00:00.000 2010-01-01 12:00:00.000
2010-01-01 11:00:00.000 2010-01-01 11:00:00.000
2010-01-01 08:00:00.000 2010-01-01 11:00:00.000
2010-01-01 07:00:00.000 2010-01-01 11:00:00.000
2010-01-01 07:00:00.000 2010-01-01 09:00:00.000
2010-01-01 16:00:00.000 2010-01-01 20:00:00.000
2010-01-01 10:00:00.000 2010-01-01 12:00:00.000
2010-01-01 09:00:00.000 2010-01-01 12:00:00.000(11 行受影响)
*/
;WITH A AS --过滤掉相同的列
 (
  SELECT DISTINCT A,B FROM TIMES 
 ),
  B AS  --排序，同时筛选出来跨度过长的列，并排除a=b,a>b的列
(
 
SELECT A,B ,DATEDIFF(SECOND,A,B) AS diff FROM A where b>a 
),
C as 
(select a,b,row_number() over(partition by a order by b) as rn, cnt=(select count(*) from b as b1 where b1.a=b.a group by a) from b ),
d as
(
 select a,b ,row_number() over(partition by b order by a) as rn from c where rn=cnt
),e as
(
 select a,b,row_number()over( order by a) as rn,datediff(second,a,b) as diff from d where rn =1
)
--此方法比较靠谱select sum(case when e1.a is not null and e2.a is null  then e.diff
            when e1.a is not null and e2.a is not null and e.b >e2.a then datediff(second,e2.b,e.b)
when e1.a is not null and e2.a is not null and e.b<=e2.a then e.diff
            when e1.a is null and e2.a is not null and  e.a<e2.b then datediff(second,e2.b,e.b)
when e1.a is null and e2.a is not null and  e.a>=e2.b then e.diff
            else 0 end )
from e left outer join e as e1 on e1.rn-1 =e.rn 
       left outer join e as e2 on e2.rn+1 =e.rn    /*
   
   
-----------
32400(1 行受影响)
   */