求教一个时间段的问题，

[baggio328]

用工作举个例子吧
比如说
job startmonth endmonth
1 1 4
1 1 5
1 2 3
1 7 9
1 8 11

想得到job 1的经历时间
按照例子的话应该是
job sum_month
1 10
不知道说明白没有，请赐教

[baggio328]

有点问题好像

表设计确实有问题

没办法，谁让咱是小兵呢

[baggio328]

我尝试了游标，但是还是不能正确统计

[fwacky]

应该是8 月
1 2 3 4 5
1 1 1 1 是 4

7 8 9 10 11
1 1 1 1 是 4

[chuifengde]

declare @a table(job int, st int, en int)
insert @a select 1, 1 ,4
union all select 1, 1, 5
union all select 1, 2, 3
union all select 1, 7, 9
union all select 1, 8, 11
union all select 1,23,45

declare @b table(a int)

select top 100 identity(int,0,1) id into #tmp from syscolumns


insert @b select b.id from #tmp b,@a a where b.id between st and en and job=1

select distinct * into #g from @b

select identity(int,1,1) id1, * into #x from #g a where not exists(select 1 from #g where a.a=a+1) order by a
select identity(int,1,1) id2, * into #y from #g a where not exists(select 1 from #g where a.a+1=a) order by a
select sum(b.a+1)-sum(a.a) from #x a,#y b where a.id1=b.id2
select * from #y

drop table #tmp,#g,#x,#y

[nettman]

Mark!

[xiaoku]

呵呵...

思路：
把包含关系的干掉，把交叉的提取两头，最好在 sum...

[wgzaaa]

测试过,
select job,stuff('000000000000',st,en-st+1,replicate('1',en-st+1))A into #tab1 from 原表

select job,0+max(substring(A,1,1))+max(substring(A,2,1))+max(substring(A,3,1))+max(substring(A,4,1))
+max(substring(A,5,1))+max(substring(A,6,1))+max(substring(A,7,1))+max(substring(A,8,1))
+max(substring(A,9,1))+max(substring(A,10,1))+max(substring(A,11,1))+max(substring(A,12,1))
from #tab1 group by job

[wgzaaa]

若拘泥于一句只要将下面的A用上面的：
stuff('000000000000',st,en-st+1,replicate('1',en-st+1))
换掉，再改一下表名即可,未考虑跨年度

[w75251455]

楼上的招试不错~~！！HOHO

[wgzaaa]

呵呵，没法跟你的 24点比

[w75251455]

--可以写成这样
select job,0+max(substring(A,1,1))+max(substring(A,2,1))+max(substring(A,3,1))+max(substring(A,4,1))
+max(substring(A,5,1))+max(substring(A,6,1))+max(substring(A,7,1))+max(substring(A,8,1))
+max(substring(A,9,1))+max(substring(A,10,1))+max(substring(A,11,1))+max(substring(A,12,1))
from(select job,stuff('000000000000',startmonth,endmonth-startmonth+1,
replicate('1',endmonth-startmonth+1))A from tt)a
group by job

[maopi9999]

111

[wgzaaa]

晕，我怎么在这上没过去,还用什么临时表，谢谢w75251455(砍破

[maopi9999]

还是用菜鸟的方法吧：select sum(endmonth-startmonth) from job----int
select sum(datediff(m,endmonth,startmonth)) from job -----datetime
菜鸟曰：简单实用，上面的看不明白。

[w75251455]

CREATE FUNCTION F_WWW (@job int)
RETURNS int
AS
BEGIN
declare @min int,@max int,@thepty int,@minup int,@maxup int
select @min=0,@max=0,@minup=0,@maxup=0,@thepty=-1

select @min=case when startmonth>@maxup and endmonth>@maxup then startmonth
else @minup end,
@max=case when startmonth>=@minup and endmonth>=@maxup then endmonth
when startmonth<=@max and endmonth>=@max then endmonth else @maxup end,
@thepty=@thepty+case when startmonth>@maxup and endmonth>@maxup then @maxup-@minup+1 else 0 end,
@minup=@min,@maxup=@max
from tt where job=@job order by startmonth

return @thepty+@maxup-@minup+1
END

drop table tt
create table TT(job int, startmonth int, endmonth int )
insert into TT select 1, 1, 4
union all select 1, 1, 5
union all select 1, 2, 3
union all select 1, 7, 9
union all select 1, 8, 11
insert into TT select 2, 1, 4
union all select 2, 1, 3
union all select 2, 8, 11


select job, dbo.F_WWW(job)[monthcount] from tt group by job


job monthcount
----------- -----------
1 10 -----------1,2,3,4,5,7,8,9,10,11 (10)
2 8 -----------1,2,3,4,8,9,10,11 (8)

[w75251455]

好题难找啊

[w75251455]

--改修了一下函数~省去了几个不必要的判断！
alter FUNCTION F_WWW(@job int)
RETURNS int
AS
BEGIN
declare @min int,@max int,@thepty int,@minup int,@maxup int
select @min=0,@max=0,@minup=0,@maxup=0,@thepty=-1

select @min=case when startmonth>@maxup and endmonth>@maxup then startmonth else @minup end,
@max=case when startmonth<@maxup and endmonth<@maxup then @maxup else endmonth end,
@thepty=@thepty+case when startmonth>@maxup and endmonth>@maxup then @maxup-@minup+1 else 0 end,
@minup=@min,@maxup=@max
from tt where job=@job order by startmonth

return @thepty+@maxup-@minup+1
END

[sp4]

select job,max(endmonth)-min(startmonth) as sum_month from t
group by job

[baggio328]

不好意思，快一个星期没上网了
我正在测试，等通过了，结贴
不好意思拖了这么久....

[baggio328]

已经测试过了
整理了一下 砍破 兄的方法跟大家分享下
wgzaaa() 和chuifengde(树上的鸟儿)的方法也可行，
不过似乎麻烦一些
多谢回帖的诸位

code
CopyRight © w75251455(砍破)
No Right Reserved
--------------------
if exists (select [name] from sysobjects
where [name] = 'F_WWW'
)
drop function F_WWW
go

CREATE FUNCTION F_WWW (@job int)
RETURNS int
AS
BEGIN
declare @min int, @max int, @thepty int, @minup int, @maxup int
select @min=0, @max=0, @minup=0, @maxup=0, @thepty=-1

select @min = case
when startmonth > @maxup and endmonth > @maxup then startmonth
else @minup end
,@max = case
when startmonth >= @minup and endmonth >= @maxup then endmonth
when startmonth <= @maxup and endmonth >= @maxup then endmonth
else @maxup end
,@thepty = @thepty + case
when startmonth > @maxup and endmonth > @maxup then @maxup-@minup+1
else 0 end
,@minup = @min
,@maxup = @max
from tt
where job = @job
order by startmonth

return @thepty + @maxup - @minup + 1
END
go

create table TT
(job int, startmonth int, endmonth int )
insert into TT
select 1, 1, 4
union all
select 1, 1, 5
union all
select 1, 2, 3
union all
select 1, 7, 9
union all
select 1, 8, 11

insert into TT
select 2, 1, 4
union all
select 2, 1, 3
union all
select 2, 8, 11

select * from tt

select job, dbo.F_WWW(job) [monthcount] from tt group by job

drop table tt