表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
39 2010-05-01 2010-06-30 10
39 2010-05-28 2010-08-30 15
... ... ... ...
40 2010-05-01 2010-06-30 10
40 2010-05-28 2010-05-30 10
40 2010-05-10 2010-05-30 10
40 2010-04-01 2010-05-10 50
... ... ... ...
41 2010-05-01 2010-05-10 10
41 2010-05-20 2010-05-30 5 42 ... ... ...
42 ...........
55 ... ... ...
要求统计出一段时间内每种编号在日期交集处的时间长度合计(取最大值)
统计日期1: 2010-05-01 至 2010-05-30
结果如下:编号 时间长度
39 55 说明:在2010-05-28到2010-05-30这段时间内容时间长度合计值最大55=10+20+10+15
40 70
41 10 统计日期2: 2010-05-01 至 2010-05-10编号 时间长度
39 35
40 70
41 5 注:统计日期和每种编号的记录数都是不定的
编号 开始日期 结束日期 时间长度
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
39 2010-05-01 2010-06-30 10
39 2010-05-28 2010-08-30 15
... ... ... ...
40 2010-05-01 2010-06-30 10
40 2010-05-28 2010-05-30 10
40 2010-05-10 2010-05-30 10
40 2010-04-01 2010-05-10 50
... ... ... ...
41 2010-05-01 2010-05-10 10
41 2010-05-20 2010-05-30 5 42 ... ... ...
42 ...........
55 ... ... ...
要求统计出一段时间内每种编号在日期交集处的时间长度合计(取最大值)
统计日期1: 2010-05-01 至 2010-05-30
结果如下:编号 时间长度
39 55 说明:在2010-05-28到2010-05-30这段时间内容时间长度合计值最大55=10+20+10+15
40 70
41 10 统计日期2: 2010-05-01 至 2010-05-10编号 时间长度
39 35
40 70
41 5 注:统计日期和每种编号的记录数都是不定的
解决方案 »
- 求一个超难的取字符串SQL语句
- 求一条联合查询语句
- 稍微复杂的去掉重复记录问题,解决马上给分!!!!!!在线等!!!再问!!!!
- 每次人多,读数据库次数增多的时候,sql数据库就占用cpu100%,有什么办法当某个连接读取数据库占用cpu达到50的时候就把该连接断掉吗??
- 海量数据排行榜问题,请教优化思路。
- IDENTITY函数到底是什么意思
- 高手指点一二,是否如此?用sp_做存储过程的开头会这样吗?
- 各位有什么意见, 北京 || 上海?
- Access 问 题
- 版主请帮忙,如何加快数据处理
- 有关sql server 2005用openrowset功能向数据库中存入图片的问题
- 请教我这个数据库该怎么办?
select 编号,SUM(时间长度) AS 时间长度 FROM 表A WHERE 起始日期>='2010-05-10' and 结束日期<='2010-05-10' GROUP BY 编号
(
编号 int,
开始日期 datetime,
结束日期 datetime,
时间长度 int
)
insert into #TT select 39,'2010-05-01','2010-06-30',10
insert into #TT select 39,'2010-04-01','2010-05-10',10
insert into #TT select 39,'2010-05-01','2010-05-20',5
insert into #TT select 39,'2010-05-25','2010-07-31',20
insert into #TT select 39,'2010-05-01','2010-06-30',10
insert into #TT select 39,'2010-05-28','2010-08-30',15
insert into #TT select 40,'2010-05-01','2010-06-30',10
insert into #TT select 40,'2010-05-28','2010-05-30',10
insert into #TT select 40,'2010-05-10','2010-05-30',10
insert into #TT select 40,'2010-04-01','2010-05-10',50
insert into #TT select 41,'2010-05-01','2010-05-10',10
insert into #TT select 41,'2010-05-20','2010-05-30',5declare @StartTime datetime
declare @EndTime datetime
set @StartTime='2010-05-01'
set @EndTime='2010-05-10'select 编号,sum(时间长度) 时间长度
from
(
select * from #TT where 开始日期<=@StartTime and @StartTime<结束日期
union all
select * from #TT where 开始日期<@EndTime and @EndTime<结束日期
) TT
group by 编号编号 时间长度
----------- -----------
39 60
40 70
41 10(3 行受影响)
顺便说下LZ的答案错啦create table #TT
(
编号 int,
开始日期 datetime,
结束日期 datetime,
时间长度 int
)
insert into #TT select 39,'2010-05-01','2010-06-30',10
insert into #TT select 39,'2010-04-01','2010-05-10',10
insert into #TT select 39,'2010-05-01','2010-05-20',5
insert into #TT select 39,'2010-05-25','2010-07-31',20
insert into #TT select 39,'2010-05-01','2010-06-30',10
insert into #TT select 39,'2010-05-28','2010-08-30',15
insert into #TT select 40,'2010-05-01','2010-06-30',10
insert into #TT select 40,'2010-05-28','2010-05-30',10
insert into #TT select 40,'2010-05-10','2010-05-30',10
insert into #TT select 40,'2010-04-01','2010-05-10',50
insert into #TT select 41,'2010-05-01','2010-05-10',10
insert into #TT select 41,'2010-05-20','2010-05-30',5declare @StartTime datetime
declare @EndTime datetime
set @StartTime='2010-05-01'
set @EndTime='2010-05-10'select 编号,sum(时间长度) 时间长度
from
(
select * from #TT where @StartTime between 开始日期 and 结束日期
or @EndTime between 开始日期 and 结束日期
) TT
group by 编号编号 时间长度
----------- -----------
39 35
40 70
41 10(3 行受影响)
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
编号 int,
开始日期 datetime,
结束日期 datetime,
时间长度 int
)
insert into tb select 39,'2010-05-01','2010-06-30',10
insert into tb select 39,'2010-04-01','2010-05-10',10
insert into tb select 39,'2010-05-01','2010-05-20',5
insert into tb select 39,'2010-05-25','2010-07-31',20
insert into tb select 39,'2010-05-01','2010-06-30',10
insert into tb select 39,'2010-05-28','2010-08-30',15
insert into tb select 40,'2010-05-01','2010-06-30',10
insert into tb select 40,'2010-05-28','2010-05-30',10
insert into tb select 40,'2010-05-10','2010-05-30',10
insert into tb select 40,'2010-04-01','2010-05-10',50
insert into tb select 41,'2010-05-01','2010-05-10',10
insert into tb select 41,'2010-05-20','2010-05-30',5
go
--建立函数
create function k
(
@StartTime datetime,
@EndTime datetime,
@bianhao int --函数
)
returns int
as
begin
declare @m_value int,@i datetime,@j datetime ,@bi int
set @m_value=0
set @bi = 0
set @i=(select MIN(beg_t) from
(select 编号,
beg_t=case when @StartTime< 开始日期 then 开始日期 else @StartTime end,
end_t=case when @EndTime > 结束日期 then 结束日期 else @EndTime end,
时间长度
from tb
where 编号=@bianhao and @StartTime <=结束日期 and @EndTime >= 开始日期)
k ) ;while (@i<=(select MAX(beg_t) from
(select 编号,
beg_t=case when @StartTime< 开始日期 then 开始日期 else @StartTime end,
end_t=case when @EndTime > 结束日期 then 结束日期 else @EndTime end,
时间长度
from tb
where 编号=@bianhao and @StartTime <=结束日期 and @EndTime >= 开始日期)k ) )
begin
set @j=@i
while(@j<=(select MAX(beg_t) from
(select 编号,
beg_t=case when @StartTime< 开始日期 then 开始日期 else @StartTime end,
end_t=case when @EndTime > 结束日期 then 结束日期 else @EndTime end,
时间长度
from tb
where 编号=@bianhao and @StartTime <=结束日期 and @EndTime >= 开始日期) k))
begin
set @m_value=(select SUM(时间长度) from
(select 编号,
beg_t=case when @StartTime< 开始日期 then 开始日期 else @StartTime end,
end_t=case when @EndTime > 结束日期 then 结束日期 else @EndTime end,
时间长度
from tb
where 编号=@bianhao and @StartTime <=结束日期 and @EndTime >= 开始日期)k where @i>=beg_t and @j<=end_t)
set @j=@j+1
if(@m_value>@bi)
set @bi=@m_value
end
set @i=@i+1
end
return @bi
end
go
--测试
declare @StartTime datetime
declare @EndTime datetime
set @StartTime='2010-05-01'
set @EndTime='2010-05-30'
select distinct 编号,dbo.k(@StartTime,@EndTime,编号) as 总数
from tb
/*
编号 总数
----------- -----------
39 55
40 70
41 10(3 行受影响)*/
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
编号 int,
开始日期 datetime,
结束日期 datetime,
时间长度 int
)
insert into tb select 39,'2010-05-01','2010-06-30',10
insert into tb select 39,'2010-04-01','2010-05-10',10
insert into tb select 39,'2010-05-01','2010-05-20',5
insert into tb select 39,'2010-05-25','2010-07-31',20
insert into tb select 39,'2010-05-01','2010-06-30',10
insert into tb select 39,'2010-05-28','2010-08-30',15
insert into tb select 40,'2010-05-01','2010-06-30',10
insert into tb select 40,'2010-05-28','2010-05-30',10
insert into tb select 40,'2010-05-10','2010-05-30',10
insert into tb select 40,'2010-04-01','2010-05-10',50
insert into tb select 41,'2010-05-01','2010-05-10',10
insert into tb select 41,'2010-05-20','2010-05-30',5
go
--用存储过程
create proc ko @StartTime datetime,
@EndTime datetime,
@bianhao int ,
@zongshu int outputas
begin
declare @m_value int,@i datetime,@j datetime ,@bi int
set @m_value=0
set @bi = 0
select 编号,
beg_t=case when @StartTime< 开始日期 then 开始日期 else @StartTime end,
end_t=case when @EndTime > 结束日期 then 结束日期 else @EndTime end,
时间长度
into #
from tb
where 编号=@bianhao and @StartTime <=结束日期 and @EndTime >= 开始日期set @i=(select MIN(beg_t) from #) ;while (@i<=(select MAX(beg_t) from # ) )
begin
set @j=@i
while(@j<=(select MAX(beg_t) from #))
begin
set @m_value=(select SUM(时间长度) from # where @i>=beg_t and @j<=end_t)
set @j=@j+1
if(@m_value>@bi)
set @bi=@m_value
end
set @i=@i+1
end
set @zongshu=@bi
end
go
--测试
--建立测试表
create table test
(
编号 int,
总数 int
)
--利用游标读取
declare @StartTime datetime
declare @EndTime datetime
set @StartTime='2010-05-01'
set @EndTime='2010-05-30'
declare cur_s cursor for
select 编号 from (select distinct 编号 from tb ) k
open cur_s
declare @n int
fetch next from cur_s into @n
while @@FETCH_STATUS=0
begin
declare @zongshu int
exec ko @StartTime ,@EndTime, @n,@zongshu output
insert test select @n,@zongshu
fetch next from cur_s into @n
end
close cur_s
deallocate cur_s select * from test drop table test
/*
编号 总数
----------- -----------
39 55
40 70
41 10(3 行受影响)*/
create table tb
(
编号 int,
开始日期 datetime,
结束日期 datetime,
时间长度 int
)
insert into tb select 39,'2010-05-01','2010-06-30',10
insert into tb select 39,'2010-04-01','2010-05-10',10
insert into tb select 39,'2010-05-01','2010-05-20',5
insert into tb select 39,'2010-05-25','2010-07-31',20
insert into tb select 39,'2010-05-01','2010-06-30',10
insert into tb select 39,'2010-05-28','2010-08-30',15
insert into tb select 40,'2010-05-01','2010-06-30',10
insert into tb select 40,'2010-05-28','2010-05-30',10
insert into tb select 40,'2010-05-10','2010-05-30',10
insert into tb select 40,'2010-04-01','2010-05-10',50
insert into tb select 41,'2010-05-01','2010-05-10',10
insert into tb select 41,'2010-05-20','2010-05-30',5
godeclare @dt1 as datetime
declare @dt2 as datetimeset @dt1 = '2010-05-28'
set @dt2 = '2010-05-31'
select 编号 , sum(时间长度) 时间长度 from tb where 开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期 group by 编号
/*
编号 时间长度
----------- -----------
39 55
40 30
41 5(所影响的行数为 3 行)
*/set @dt1 = '2010-05-01'
set @dt2 = '2010-05-30'
select 编号 , sum(时间长度) 时间长度 from tb where 开始日期 between @dt1 and @dt2 or 结束日期 between @dt1 and @dt2 or @dt1 between 开始日期 and 结束日期 or @dt2 between 开始日期 and 结束日期 group by 编号
/*
编号 时间长度
----------- -----------
39 70
40 80
41 15(所影响的行数为 3 行)
*/
drop table tb
create table tb
(
编号 int,
开始日期 datetime,
结束日期 datetime,
时间长度 int
)
insert into tb select 39,'2010-05-01','2010-06-30',10
insert into tb select 39,'2010-04-01','2010-05-10',10
insert into tb select 39,'2010-05-01','2010-05-20',5
insert into tb select 39,'2010-05-25','2010-07-31',20
insert into tb select 39,'2010-05-01','2010-06-30',10
insert into tb select 39,'2010-05-28','2010-08-30',15
insert into tb select 40,'2010-05-01','2010-06-30',10
insert into tb select 40,'2010-05-28','2010-05-30',10
insert into tb select 40,'2010-05-10','2010-05-30',10
insert into tb select 40,'2010-04-01','2010-05-10',50
insert into tb select 41,'2010-05-01','2010-05-10',10
insert into tb select 41,'2010-05-20','2010-05-30',5
gocreate 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 35
40 20
41 15(所影响的行数为 3 行)
*/exec my_proc '2010-05-28 ' ,'2010-05-31'
/*
编号 时间长度
----------- -----------
39 35
40 20
41 5(所影响的行数为 3 行)
*/drop procedure my_proc
drop table tb,tmp
declare @DateEnd as date='2010-05-30';WITH T(Dayc)
as
(select @DateBegin
union all
select DATEADD(DD,1,dayC) FROM T WHERE DAYC<=@DateEnd
)
SELECT DISTINCT B.编号,MAX(B.总数) OVER (PARTITION BY B.编号)总数 FROM
(SELECT T.Dayc , a.编号 , SUM(A.时间长度) 总数
FROM T
JOIN tb A ON T.Dayc BETWEEN A.开始日期 AND A.结束日期
group by A.编号 ,T.Dayc ) B
这里不要=号,应为
select DATEADD(DD,1,dayC) FROM T WHERE DAYC<@DateEnd