这个统计的求SQL语句怎么写？

Question

表A
编号     开始日期         结束日期           时间长度
-------- ------------------------------- ----------------
39       2010-05-01      2010-06-30            10
39       2010-04-01      2010-05-10            10
39       2010-05-01      2010-05-20            5
39       2010-05-25      2010-07-31            20
...
40       2010-03-18      2010-10-01            10
40       2010-05-01      2010-05-31            5
...
41       2010-05-01      2010-05-10            5
41       2010-05-10      2010-07-31            20
41       2010-05-20      2010-10-01            5
....要求算出2010-05-01 到 2010-05-30这段时间内每种编号每天的时间长度相加最长是多少编号  时间长度
39     30  （注：第1条记录和第4条记录交集的日期内时间最长 所以等10+20 =30）
40     15
41     25
...查询时间范围 2010-05-20 到 2010-05-31
编号  时间长度
39     30
40     15
41     25
...查询时间范围 2010-07-20 到 2010-08-19
编号  时间长度
39     20
41     25
...
 

Answer 1

/*
*************************************
*   T-MAC 小编                      *
*        -->努力成长中              *
*        -->梦想DBA                 *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb 
go
create table tb (编号 int,  开始日期 datetime ,结束日期 datetime , 时间长度 int )
insert tb select
39, '2010-05-01', '2010-06-30', 10 union select 
39, '2010-04-01', '2010-05-10', 10 union select
39, '2010-05-01', '2010-05-20', 5 union select
39, '2010-05-25', '2010-07-31', 20 union select
40, '2010-03-18', '2010-10-01', 10 union select
40, '2010-05-01', '2010-05-31', 5 union select
41, '2010-05-01', '2010-05-10', 5 union select
41, '2010-05-10', '2010-07-31', 20 union select
41, '2010-05-20', '2010-10-01', 5 
go
declare @be datetime ,@end datetime
set @be='2010-05-01'
set @end='2010-05-30'
select 编号,SUM(时间长度) as 时间长度
from tb 
where @be<=结束日期 and @end between 开始日期 and 结束日期 
group by 编号 
/*
编号          时间长度
----------- -----------
39          30
40          15
41          25
*/

Answer 2

改成这样
感觉楼主最后那个结果又问题也许我理解错了 
declare @be datetime ,@end datetime
set @be='2010-07-20'
set @end='2010-08-19'
select 编号,SUM(时间长度) as 时间长度
from tb 
where @be<结束日期 and @end >开始日期 
group by 编号 
/*
编号          时间长度
----------- -----------
39          20
40          10
41          25
*/

Answer 3

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-23 01:54:44
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([编号] INT,[开始日期] DATETIME,[结束日期] DATETIME,[时间长度] INT)
INSERT [a]
SELECT 39,N'2010-05-01',N'2010-06-30',10 UNION ALL
SELECT 39,N'2010-04-01',N'2010-05-10',10 UNION ALL
SELECT 39,N'2010-05-01',N'2010-05-20',5 UNION ALL
SELECT 39,N'2010-05-25',N'2010-07-31',20 UNION ALL
SELECT 40,N'2010-03-18',N'2010-10-01',10 UNION ALL
SELECT 40,N'2010-05-01',N'2010-05-31',5 UNION ALL
SELECT 41,N'2010-05-01',N'2010-05-10',5 UNION ALL
SELECT 41,N'2010-05-10',N'2010-07-31',20 UNION ALL
SELECT 41,N'2010-05-20',N'2010-10-01',5
GO
--SELECT * FROM [a]-->SQL查询如下:IF NOT OBJECT_ID('[pr_test]') IS NULL
DROP PROC [pr_test]
GO
CREATE PROC [pr_test] 
@bt datetime,
@et datetime
AS
WITH T AS
(
SELECT * FROM A
WHERE @BT<结束日期 AND @ET > 开始日期 
)
,T1 AS
(
SELECT RN=ROW_NUMBER()OVER(PARTITION BY 编号 ORDER BY 时间长度 DESC),* FROM T A
WHERE 结束日期<(SELECT MAX(结束日期) FROM T WHERE 编号=A.编号)
UNION  
SELECT 1,* FROM T A 
WHERE 结束日期=(SELECT MAX(结束日期) FROM T WHERE 编号=A.编号)
)
SELECT 编号,SUM(时间长度) 时间长度 
FROM T1 
WHERE RN=1
GROUP BY 编号
GO
--1.
EXEC [pr_test] '2010-05-20','2010-05-31'
/*
编号          时间长度
----------- -----------
39          30
40          15
41          25(3 行受影响)
*/
EXEC [pr_test] '2010-07-20','2010-08-19'
/*
编号          时间长度
----------- -----------
39          20
40          10
41          25(3 行受影响)
*/

Answer 4

--用了存储过程,临时表,两个游标,做是做出来了,不知道你能不能看懂.
create table tb (编号 int, 开始日期 datetime ,结束日期 datetime , 时间长度 int )
insert tb select
39, '2010-05-01', '2010-06-30', 10 union select 
39, '2010-04-01', '2010-05-10', 10 union select
39, '2010-05-01', '2010-05-20', 5 union select
39, '2010-05-25', '2010-07-31', 20 union select
40, '2010-03-18', '2010-10-01', 10 union select
40, '2010-05-01', '2010-05-31', 5 union select
41, '2010-05-01', '2010-05-10', 5 union select
41, '2010-05-10', '2010-07-31', 20 union select
41, '2010-05-20', '2010-10-01', 5 
create table tmp(编号 int,开始日期1 datetime , 结束日期1 datetime , 开始日期2 datetime , 结束日期2 datetime,时间长度1 int,时间长度2 int , 数量 int)
gocreate procedure my_proc @dt1 datetime,@dt2 datetime
as
begin
delete from tmp
declare @编号1 int,@编号2 int
declare @开始日期1 datetime,@结束日期1 datetime
declare @开始日期2 datetime,@结束日期2 datetime
declare @时间长度1 int,@时间长度2 int;
set @编号2 = 0
declare cur1 cursor fast_forward for
  select * from tb where 开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期 order by 编号 , 开始日期 ,结束日期;
open cur1;
fetch next from cur1 into @编号1,@开始日期1,@结束日期1,@时间长度1;
while @@fetch_status=0
begin
   if @编号1 <> @编号2
      begin
        set @编号2 = @编号1
      end
   else
      begin
        declare cur2 cursor fast_forward for select 开始日期 ,结束日期 , 时间长度 from tb where (编号 = @编号1 and 开始日期 > @开始日期1) and (开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期);
        open cur2;
        fetch next from cur2 into @开始日期2,@结束日期2,@时间长度2;
        while @@fetch_status=0
        begin
          declare @cnt int
          set @cnt = (select count(1) from
                     (
                       select dt from 
                       (
                         select dateadd(dd,num,@开始日期1) dt from 
                         (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
                         where dateadd(dd,num,@开始日期1)<=@结束日期1
                         union all
                         select dateadd(dd,num,@开始日期2) dt from 
                         (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
                         where dateadd(dd,num,@开始日期2)<=@结束日期2
                       ) m 
                       group by dt having count(1) > 1
                     ) t)
          insert into tmp values(@编号1,@开始日期1,@结束日期1,@开始日期2,@结束日期2,@时间长度1,@时间长度2,@cnt) 
          fetch next from cur2 into @开始日期2,@结束日期2,@时间长度2;
        end
        close cur2;
        deallocate cur2;
      end
   fetch next from cur1 into @编号1,@开始日期1,@结束日期1,@时间长度1;
end
close cur1;
deallocate cur1;select 编号,sum(时间长度) 时间长度 from tb where (开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期) and 编号 not in (select 编号 from tmp) group by 编号
union all
select t.编号 , sum(时间长度1 + 时间长度2) 时间长度 from tmp t where 数量 = (select top 1 数量 from tmp where 编号 = t.编号 order by 数量 desc) group by 编号
order by 编号
end
goexec my_proc '2010-05-01' , '2010-05-31'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-05-20 ' ,'2010-05-31'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-07-20 ' ,'2010-08-19'
/*
编号          时间长度        
----------- ----------- 
39          20
40          10
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-04-01' , '2010-10-30'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/drop procedure my_proc
drop table tb,tmp

Answer 5

--稍做更改为如下:
create table tb (编号 int, 开始日期 datetime ,结束日期 datetime , 时间长度 int )
insert tb select
39, '2010-05-01', '2010-06-30', 10 union select 
39, '2010-04-01', '2010-05-10', 10 union select
39, '2010-05-01', '2010-05-20', 5 union select
39, '2010-05-25', '2010-07-31', 20 union select
40, '2010-03-18', '2010-10-01', 10 union select
40, '2010-05-01', '2010-05-31', 5 union select
41, '2010-05-01', '2010-05-10', 5 union select
41, '2010-05-10', '2010-07-31', 20 union select
41, '2010-05-20', '2010-10-01', 5 
create table tmp(编号 int,开始日期1 datetime , 结束日期1 datetime , 开始日期2 datetime , 结束日期2 datetime,时间长度1 int,时间长度2 int , 数量 int)
gocreate procedure my_proc @dt1 datetime,@dt2 datetime
as
begin
delete from tmp
declare @编号 int
declare @开始日期1 datetime,@结束日期1 datetime
declare @开始日期2 datetime,@结束日期2 datetime
declare @时间长度1 int,@时间长度2 int;
declare cur1 cursor fast_forward for
  select * from tb where 开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期 order by 编号 , 开始日期 ,结束日期;
open cur1;
fetch next from cur1 into @编号,@开始日期1,@结束日期1,@时间长度1;
while @@fetch_status=0
begin
        declare cur2 cursor fast_forward for select 开始日期 ,结束日期 , 时间长度 from tb where (编号 = @编号 and 开始日期 > @开始日期1) and (开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期);
        open cur2;
        fetch next from cur2 into @开始日期2,@结束日期2,@时间长度2;
        while @@fetch_status=0
        begin
          declare @cnt int
          set @cnt = (select count(1) from
                     (
                       select dt from 
                       (
                         select dateadd(dd,num,@开始日期1) dt from 
                         (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
                         where dateadd(dd,num,@开始日期1)<=@结束日期1
                         union all
                         select dateadd(dd,num,@开始日期2) dt from 
                         (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
                         where dateadd(dd,num,@开始日期2)<=@结束日期2
                       ) m 
                       group by dt having count(1) > 1
                     ) t)
          insert into tmp values(@编号,@开始日期1,@结束日期1,@开始日期2,@结束日期2,@时间长度1,@时间长度2,@cnt) 
          fetch next from cur2 into @开始日期2,@结束日期2,@时间长度2;
        end
        close cur2;
        deallocate cur2;
   fetch next from cur1 into @编号,@开始日期1,@结束日期1,@时间长度1;
end
close cur1;
deallocate cur1;select 编号,sum(时间长度) 时间长度 from tb where (开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期) and 编号 not in (select 编号 from tmp) group by 编号
union all
select t.编号 , sum(时间长度1 + 时间长度2) 时间长度 from tmp t where 数量 = (select top 1 数量 from tmp where 编号 = t.编号 order by 数量 desc) group by 编号
order by 编号
end
goexec my_proc '2010-05-01' , '2010-05-31'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-05-20 ' ,'2010-05-31'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-07-20 ' ,'2010-08-19'
/*
编号          时间长度        
----------- ----------- 
39          20
40          10
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-04-01' , '2010-10-30'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/drop procedure my_proc
drop table tb,tmp

Answer 6

create table tb (编号 int,  开始日期 datetime ,结束日期 datetime , 时间长度 int )
insert tb select
39, '2010-05-01', '2010-06-30', 10 union select 
39, '2010-04-01', '2010-05-10', 10 union select
39, '2010-05-01', '2010-05-20', 5 union select
39, '2010-05-25', '2010-07-31', 20 union select
40, '2010-03-18', '2010-10-01', 10 union select
40, '2010-05-01', '2010-05-31', 5 union select
41, '2010-05-01', '2010-05-10', 5 union select
41, '2010-05-10', '2010-07-31', 20 union select
41, '2010-05-20', '2010-10-01', 5 select 编号,sum(时间长度) AS '时间长度' FROM TB WHERE 开始日期>='2010-05-01' AND 结束日期<='2010-07-01' GROUP BY 编号

Answer 7

睡了一晚,觉得有点点小错误,再次更改为如下:
create table tb (编号 int, 开始日期 datetime ,结束日期 datetime , 时间长度 int )
insert tb select
39, '2010-05-01', '2010-06-30', 10 union select 
39, '2010-04-01', '2010-05-10', 10 union select
39, '2010-05-01', '2010-05-20', 5 union select
39, '2010-05-25', '2010-07-31', 20 union select
40, '2010-03-18', '2010-10-01', 10 union select
40, '2010-05-01', '2010-05-31', 5 union select
41, '2010-05-01', '2010-05-10', 5 union select
41, '2010-05-10', '2010-07-31', 20 union select
41, '2010-05-20', '2010-10-01', 5 
create table tmp(编号 int,开始日期1 datetime , 结束日期1 datetime , 开始日期2 datetime , 结束日期2 datetime,时间长度1 int,时间长度2 int , 数量 int)
gocreate procedure my_proc @dt1 datetime,@dt2 datetime
as
begin
delete from tmp
declare @编号 int
declare @开始日期1 datetime,@结束日期1 datetime
declare @开始日期2 datetime,@结束日期2 datetime
declare @时间长度1 int,@时间长度2 int;
declare cur1 cursor fast_forward for
  select * from tb where 开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期 order by 编号 , 开始日期 ,结束日期;
open cur1;
fetch next from cur1 into @编号,@开始日期1,@结束日期1,@时间长度1;
while @@fetch_status=0
begin
        declare cur2 cursor fast_forward for select 开始日期 ,结束日期 , 时间长度 from tb where (编号 = @编号 and ((开始日期 > @开始日期1) or (开始日期 = @开始日期1 and 结束日期 > @结束日期1))) and (开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期);
        open cur2;
        fetch next from cur2 into @开始日期2,@结束日期2,@时间长度2;
        while @@fetch_status=0
        begin
          declare @cnt int
          set @cnt = (select count(1) from
                     (
                       select dt from 
                       (
                         select dateadd(dd,num,@开始日期1) dt from 
                         (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
                         where dateadd(dd,num,@开始日期1)<=@结束日期1
                         union all
                         select dateadd(dd,num,@开始日期2) dt from 
                         (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
                         where dateadd(dd,num,@开始日期2)<=@结束日期2
                       ) m 
                       group by dt having count(1) > 1
                     ) t)
          insert into tmp values(@编号,@开始日期1,@结束日期1,@开始日期2,@结束日期2,@时间长度1,@时间长度2,@cnt) 
          fetch next from cur2 into @开始日期2,@结束日期2,@时间长度2;
        end
        close cur2;
        deallocate cur2;
   fetch next from cur1 into @编号,@开始日期1,@结束日期1,@时间长度1;
end
close cur1;
deallocate cur1;select 编号,sum(时间长度) 时间长度 from tb where (开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期) and 编号 not in (select 编号 from tmp) group by 编号
union all
select t.编号 , sum(时间长度1 + 时间长度2) 时间长度 from tmp t where 数量 = (select top 1 数量 from tmp where 编号 = t.编号 order by 数量 desc) group by 编号
order by 编号
end
goexec my_proc '2010-05-01' , '2010-05-31'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-05-20 ' ,'2010-05-31'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-07-20 ' ,'2010-08-19'
/*
编号          时间长度        
----------- ----------- 
39          20
40          10
41          25（所影响的行数为 3 行）
*/exec my_proc '2010-04-01' , '2010-10-30'
/*
编号          时间长度        
----------- ----------- 
39          30
40          15
41          25（所影响的行数为 3 行）
*/drop procedure my_proc
drop table tb,tmp

Answer 8

谢谢大家的热心帮助 特别是htl258 和 dawugui(爱新觉罗.毓华) 可能是我没把问题说清楚 大家的方法都不能实现我的要求
下面我再把我的问题写一下    <table border="1" style="width:290;">
        <tr>
            <td width="50">
                编号</td>
            <td width="80">
                开始日期</td>
            <td width="80">
                结束日期</td>
            <td width="80">
                时间长度</td>   
        </tr>
        <tr>
            <td>
                39</td>
            <td>
                2010-05-01</td>
            <td>
                2010-06-30</td>
            <td>
                10</td>
        </tr>
        <tr>
            <td>
                39</td>
            <td>
                2010-04-01</td>
            <td>
                2010-05-10</td>
            <td>
                10</td>
        </tr>
         <tr>
            <td>
                39</td>
            <td>
                2010-05-01</td>
            <td>
                2010-05-20</td>
            <td>
                5</td>
        </tr>
         <tr>
            <td>
                39</td>
            <td>
                2010-05-25</td>
            <td>
                2010-07-31</td>
            <td>
                20</td>
        </tr>
         <tr>
            <td>
                39</td>
            <td>
                2010-05-01</td>
            <td>
                2010-06-30</td>
            <td>
                10</td>
        </tr>
        <tr>
            <td>
                39</td>
            <td>
                2010-05-28</td>
            <td>
                2010-08-30</td>
            <td>
                15</td>
        </tr>
        <tr>
            <td>
                ...</td>
            <td>
                ...</td>
            <td>
                ...</td>
            <td>
                ...</td>
        </tr>
        <tr>
            <td>
                40</td>
            <td>
                2010-05-01</td>
            <td>
                2010-06-30</td>
            <td>
                10</td>
        </tr>
        <tr>
            <td>
                40</td>
            <td>
                2010-05-28</td>
            <td>
                2010-05-30</td>
            <td>
                10</td>
        </tr>
        <tr>
            <td>
                40</td>
            <td>
                2010-05-10</td>
            <td>
                2010-05-30</td>
            <td>
                10</td>
        </tr>
        <tr>
            <td>
                40</td>
            <td>
                2010-04-01</td>
            <td>
                2010-05-10</td>
            <td>
                50</td>
        </tr>
        <tr>
            <td>
                ...</td>
            <td>
                ...</td>
            <td>
                ...</td>
            <td>
                ...</td>
        </tr>
        <tr>
            <td>
                41</td>
            <td>
                2010-05-01</td>
            <td>
                2010-05-10</td>
            <td>
                10</td>
        </tr>
        <tr>
            <td>
                41</td>
            <td>
                2010-05-20</td>
            <td>
                2010-05-30</td>
            <td>
                5</td>
        </tr>
        <tr>
            <td>
                42</td>
            <td>
                ...</td>
            <td>
                ...</td>
            <td>
                ...</td>
        </tr>
        <tr>
            <td>
                55</td>
            <td>
                ...</td>
            <td>
                ...</td>
            <td>
                ...</td>
        </tr>
    </table>
    要求统计出一段时间内每种编号在日期交集处的时间长度合计（取最大值）<br />
    统计日期1:&nbsp; 2010-05-01 至 2010-05-30 <br />
    结果如下：<br>
    <table border="1" cellpadding="0" cellspacing="0">
        <tr>
            <td>编号</td>
            <td>时间长度</td>
            <td></td>
        </tr>
        <tr>
            <td>39</td>
            <td>55</td>
            <td>说明：在2010-05-28到2010-05-30这段时间内容时间长度合计值最大 55=10+20+10+15&nbsp; </td>
        </tr>
        <tr>
            <td>40</td>
            <td>70</td>
            <td></td>
        </tr>
         <tr>
            <td>41</td>
            <td>10</td>
            <td></td>
        </tr>
    </table>
    统计日期2:&nbsp; 2010-05-01 至 2010-05-10<br />
    <table border="1" cellpadding="0" cellspacing="0">
        <tr>
            <td>编号</td>
            <td>时间长度</td>
        </tr>
        <tr>
            <td>39</td>
            <td>35</td>
        </tr>
        <tr>
            <td>40</td>
            <td>70</td>
        </tr>
        <tr>
            <td>41</td>
            <td>5</td>
        </tr>
    </table>