征求SQL语句

[nuaawyd]

现在要从数据库中取出数据在某一个时点的峰值，每个数据都是有时限的，要在时效的范围内数据的最大总和。具体数据如下：
项目名称 开始日期 数值 结束日期
a 20050617 100 20060516
b 20060103 300 20060817
c 20050923 400 20070318
d 20070711 200 20080102

在这个数据示例中要取出的就是有时间交集的a,b,c三项的数值总和100＋300＋400

[dawugui]

我那个是偷机的,不能算完全正确.

如果第一条记录在后面的记录中不交叉,就不行了.

反正他这个问题不好搞.

[nuaawyd]

dawugui ，你好

你的程序可能有点问题，我有个数据验证了一下，具体真实数据如下
项目名称 开始日期 数值 结束日期
a 20060111 21850000 20060711
b 20060307 13300000 20060906
c 20060831 11400000 20070228
d 20051017 8500000 20060117

理论上应该是a,b,d三项的和，实际运算结果是a和d的和，请帮忙看一下问题出在什么地方，谢谢

[昵称被占用了]

贴个正解

--> 测试数据：@tb
declare @tb table([项目名称] varchar(1),[开始日期] datetime,[数值] int,[结束日期] datetime)
insert @tb
select 'a','20050617',100,'20060516' union all
select 'b','20060103',300,'20060817' union all
select 'c','20050923',400,'20070318' union all
select 'd','20070711',200,'20080102'



--时间段
declare @time table (
日期 datetime,
序号 int IDENTITY(1,1)
)
insert @time
select 开始日期 as 日期 from @tb
union 
select 结束日期 as 日期 from @tb
order by 日期


select top 1 sum(a.数值) as 数值 from @tb a ,@Time t1,@Time t2
where t2.序号=t1.序号+1
and (a.开始日期 <= t2.日期
and a.结束日期 >= t1.日期
)
group by t1.日期,t2.日期
order by sum(a.数值) desc

[dawugui]

create table tb(项目名称 varchar(10) , 开始日期 datetime, 数值 bigint, 结束日期 datetime) 
insert into tb values('a',        '20060111' , 21850000 , '20060711')  
insert into tb values('b',        '20060307' , 13300000 , '20060906') 
insert into tb values('c',        '20060831' , 11400000 , '20070228')  
insert into tb values('d',        '20051017' , 8500000  , '20060117')  
go

--临时表
select top 8000 identity(int,0,1) as id into tmp from syscolumns a,syscolumns b

select t1.数值 + t2.数值 数值 from tb t1,
(
  select sum(distinct n.数值) 数值 from tb m,
  (select 项目名称 , dateadd(day , tmp.id , 开始日期) 日期 , 数值 from tb , tmp where dateadd(day , tmp.id , 开始日期) <= 结束日期) n,
  (select top 1 项目名称 from tb order by 项目名称) o
  where m.项目名称 = o.项目名称 and n.项目名称 <> o.项目名称 and n.日期 between m.开始日期 and m.结束日期
) t2,
(select top 1 项目名称 from tb order by 项目名称) t3
where t1.项目名称 = t3.项目名称

drop table tb , tmp

/*
数值                   
-------------------- 
43650000

（所影响的行数为 1 行）
*/

[昵称被占用了]

修正下，用新数据测试

--> 测试数据：@tb
declare @tb table([项目名称] varchar(1),[开始日期] datetime,[数值] int,[结束日期] datetime)
insert @tb
/*
select 'a','20050617',100,'20060516' union all
select 'b','20060103',300,'20060817' union all
select 'c','20050923',400,'20070318' union all
select 'd','20070711',200,'20080102'
*/
select 
'a',        '20060111',  21850000,  '20060711'
union all select  
'b',        '20060307',  13300000,  '20060906' 
union all select  
'c',        '20060831',  11400000,  '20070228'  
union all select  
'd',        '20051017',  8500000 , '20060117'  

--时间段
declare @time table (
日期 datetime,
序号 int IDENTITY(1,1)
)
insert @time
select 开始日期 as 日期 from @tb
union 
select 结束日期 as 日期 from @tb
order by 日期


select top 1 sum(a.数值) as 数值 from @tb a ,@Time t1,@Time t2
where t2.序号=t1.序号+1
and (a.开始日期 < t2.日期
and a.结束日期 > t1.日期
)
group by t1.日期,t2.日期
order by sum(a.数值) desc

--结果
数值          
----------- 
35150000

（所影响的行数为 1 行）

[昵称被占用了]

这种问题需要把时间分段，计算每段的总数值，再取最大的，思路就这样，有了思路语句不难

[nuaawyd]

很奇怪，我把真实的表名和字段名替换后就得不到真实的数据

[dobear_0922]

[Quote=引用 11 楼 nuaawyd 的回复:]
dawugui ，你好

你的程序可能有点问题，我有个数据验证了一下，具体真实数据如下
项目名称 开始日期 数值 结束日期
a 20060111 21850000 20060711
b 20060307 13300000 20060906
c 20060831 11400000 20070228
d 20051017 8500000 20060117

理论上应该是a,b,d三项的和，实际运算结果是a和d的和，请帮忙看一下问题出在什么地方，谢谢
[/Quote]
这个应该是a,b的和吧？b,d没有重叠，，，

[nuaawyd]

结果应该是43650000

[dobear_0922]

--> 测试数据：@tb
declare @tb table([项目名称] varchar(1),[开始日期] datetime,[数值] int,[结束日期] datetime)
/*insert @tb
select 'a','20050617',100,'20060516' union all
select 'b','20060103',300,'20060817' union all
select 'c','20050923',400,'20070318' union all
select 'd','20070711',200,'20080102'*/
insert into @tb values('a',        '20060111' , 21850000 , '20060711')  
insert into @tb values('b',        '20060307' , 13300000 , '20060906') 
insert into @tb values('c',        '20060831' , 11400000 , '20070228')  
insert into @tb values('d',        '20051017' , 8500000  , '20060117') 

--select * from @tb

declare @StartDate datetime, @EndDate datetime

select @StartDate = min([开始日期]), @EndDate=max([结束日期]) from @tb

declare @Date table(Date datetime)

set nocount on
while @StartDate<@EndDate
begin
	insert @Date select @StartDate
	set @StartDate=@StartDate+1
end
set nocount off

select max(Total) from
(
	select Date, Total=sum(case when Date between [开始日期] and [结束日期] then [数值] else 0 end)
	from @tb, @Date
	group by Date
) T

/*
-----------
800

(1 行受影响)
*/ 
/*
-----------
35150000

(1 行受影响)
*/ 

[nuaawyd]

dobear_0922 你说的对，我看错了
那就是说应该用Haiwer 的方法了，dawugui 的方法什么地方有问题呢？

[dawugui]

[Quote=引用 20 楼 nuaawyd 的回复:]
dobear_0922 你说的对，我看错了
那就是说应该用Haiwer 的方法了，dawugui 的方法什么地方有问题呢？
[/Quote]
我是考虑第一条和第二条,第一条和第四条都有存在重复,也许我理解错了.

[nuaawyd]

是应该取存在重复的最大值

[昵称被占用了]

时间的判断可能不对，没仔细想

就是这两个条件
and (a.开始日期 < t2.日期
and a.结束日期 > t1.日期
)

[dobear_0922]

海老师的算法好，比我的效率要高很多，学习，，，

[昵称被占用了]

[Quote=引用 17 楼 dobear_0922 的回复:]
引用 11 楼 nuaawyd 的回复:
dawugui ，你好

你的程序可能有点问题，我有个数据验证了一下，具体真实数据如下
项目名称 开始日期 数值 结束日期
a 20060111 21850000 20060711
b 20060307 13300000 20060906
c 20060831 11400000 20070228
d 20051017 8500000 20060117

理论上应该是a,b,d三项的和，实际运算结果是a和d的和，请帮忙看一下问题出在什么地方…
[/Quote]
看来我的判断没错，不过><还是>= <=需要考虑下

[nuaawyd]

谢谢大家了，也辛苦各位了，结帖

[gulunhua]

我做的这个，也能得出结果：
declare @temp table(a varchar(10) , starttime datetime, num int,endtime datetime)

declare @temp_max table(a varchar(10) ,num int,sum_num int)

insert @temp
select * from tb order by starttime,endtime

insert @temp_max
select t1.a,t1.num,sum(t2.num)
from @temp as t1,@temp as t2
where t1.a<>t2.a and (t2.starttime between t1.starttime and t1.endtime) group by t1.a,t1.num

select * from @temp_max

select max(num+sum_num) from @temp_max

[gulunhua]

我把上面的补全了一下：

if(object_id('tb')) is not null
drop table tb
go

create table tb (a varchar(10) , starttime datetime, num int,endtime datetime)

insert into tb values('a', '20060111' , 21850000 , '20060711')
insert into tb values('b', '20060307' , 13300000 , '20060906')
insert into tb values('c', '20060831' , 11400000 , '20070228')
insert into tb values('d', '20051017' , 8500000 , '20060117')

declare @temp table(a varchar(10) , starttime datetime, num int,endtime datetime)

declare @temp_max table(a varchar(10) ,num int,sum_num int) --存放多个可能的结果

insert @temp
select * from tb order by starttime,endtime

insert @temp_max
select t1.a,t1.num,sum(t2.num)
from @temp as t1,@temp as t2
where t1.a<>t2.a and (t2.starttime between t1.starttime and t1.endtime) group by t1.a,t1.num

select * from @temp_max

select a,max(num+sum_num) as '总和' from @temp_max group by a order by max(num+sum_num) desc

select max(num+sum_num) as '最大值' from @temp_max

[xhlvsxzj]

select UCID,Rate as rate from rsuser where userid=@userId and startDate<=@StartDate
and @EndDate<=endDate --内交集
union all
select UCID,Rate as rate from rsuser where userid=@userId and startDate<=@StartDate
and @StartDate <= endDate --左交集
union all
select UCID,Rate as rate from rsuser where userid=@userId and startDate<=@EndDate
and @EndDate <= endDate --右交集
union all
select UCID,Rate as rate from rsuser where userid=@userId and startDate>=@StartDate
and endDate <= @EndDate --外交集