求连续最大天数对应的起始和终止日期？解决办法
日期：2014-05-18 浏览次数：20594 次
求连续最大天数对应的起始和终止日期？
测试数据的生成，连续最大天数的求解，这里给出：（以1个月为例）
/*
create table LoginTable (name varchar(10),logindate smalldatetime)
declare @i int
set @i=0
while @i<400
begin
insert into LoginTable values(char(abs(CHECKSUM(newid()))%26+65),dateadd(dd,abs(CHECKSUM(newid()))%31,'2011-01-01'))
select @i=@i+1
end
drop table #temp
select distinct name,logindate
into #temp
from LoginTable
truncate table LoginTable
insert into LoginTable select * from #temp
select * from LoginTable order by name,logindate
*/
if object_id('fcon_max') is not null
drop function fcon_max
go
--由连续天数获得一个参数@n
--function作用：由@n得到连续1的最大数目
create function fcon_max(@n bigint)
returns int
as
begin
declare @max int,@t int,@j int
select @max=0,@t=0
while (@n>0)
begin
if (@n%2=0)
begin
if (@max<@t) set @max=@t
set @t=0
end
else set @t=@t+1
set @n=@n/2
end
select @j=case when @t<@max then @max else @t end
return @j
end
go
--选取前100位name的数据
select top 100 name,dbo.fcon_max(loginday) as Logindays
from
(
SELECT name,sum(power(cast(2 as bigint),day(logindate)-1)) as loginday
FROM logintable group by name
) final
问题：每个name的最大连续天数logindays，由上述程序已经知道了。
按这个思路，怎么得到每个name的logindays所对应的起始和终止日期：strat_day和end_day?
即最终形式的表为：（name，strat_day，end_day，logindays）
请各位畅言，有好的想法也可以。。。
------解决方案--------------------
测试数据的生成，连续最大天数的求解，这里给出：（以1个月为例）
/*
create table LoginTable (name varchar(10),logindate smalldatetime)
declare @i int
set @i=0
while @i<400
begin
insert into LoginTable values(char(abs(CHECKSUM(newid()))%26+65),dateadd(dd,abs(CHECKSUM(newid()))%31,'2011-01-01'))
select @i=@i+1
end
drop table #temp
select distinct name,logindate
into #temp
from LoginTable
truncate table LoginTable
insert into LoginTable select * from #temp
select * from LoginTable order by name,logindate
*/
if object_id('fcon_max') is not null
drop function fcon_max
go
--由连续天数获得一个参数@n
--function作用：由@n得到连续1的最大数目
create function fcon_max(@n bigint)
returns int
as
begin
declare @max int,@t int,@j int
select @max=0,@t=0
while (@n>0)
begin
if (@n%2=0)
begin
if (@max<@t) set @max=@t
set @t=0
end
else set @t=@t+1
set @n=@n/2
end
select @j=case when @t<@max then @max else @t end
return @j
end
go
--选取前100位name的数据
select top 100 name,dbo.fcon_max(loginday) as Logindays
from
(
SELECT name,sum(power(cast(2 as bigint),day(logindate)-1)) as loginday
FROM logintable group by name
) final
问题：每个name的最大连续天数logindays，由上述程序已经知道了。
按这个思路，怎么得到每个name的logindays所对应的起始和终止日期：strat_day和end_day?
即最终形式的表为：（name，strat_day，end_day，logindays）
请各位畅言，有好的想法也可以。。。
------解决方案--------------------
- SQL code
--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([proname] varchar(2),[qty] int,[vtype] int,[optime] datetime)
insert [tbl]
select 'AA',-10,30,'2012-01-09' union all
select 'AA',21,31,'2012-02-14' union all
select 'AA',12,31,'2012-02-11' union all
select 'AA',-2,30,'2012-03-11' union all
select 'BB',-1,30,'2012-02-12' union all
select 'BB',90,31,'2012-02-11' union all
select 'BB',90,31,'2012-03-17' union all
select 'BB',-6,30,'2012-02-19'
select [proname],SUM(case when [vtype] = 31 THEN [qty] ELSE 0 END) as xsqty,
SUM(CASE WHEN [vtype] = 30 THEN [qty] ELSE 0 END) AS xtqty,
(select convert(varchar(10),max([optime]),120) from tbl b where a.proname=b.proname and [vtype] = 31) as lastxstime ,
(select convert(varchar(10),max([optime]),120) from tbl b where a.proname=b.proname and [vtype] = 30) as lastxstime
from tbl a
group by [proname]
/*
proname xsqty xtqty lastxstime lastxstime
AA 33 -12 2012-02-14 2012-03-11
BB 180 -7 2012-03-17 2012-02-19
*/
你看看这个例子对你有没有帮助
我有点不怎么明白你的问题
------解决方案--------------------
- SQL code
-> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([name] varchar(2),[logindate] date)
insert [tbl]
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-12' union all
select 'a1','2011-1-13' union all
select 'a1','2011-1-16' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-13' union all
select 'a2','2011-1-24'
with t as(
select [name],[logindate],(select min(b.[logindate]) from tbl b
where b.[logindate]>=a.[logindate] and b.name=a.name
and not exists (select * from tbl c
where c.[logindate]=dateadd(dd,1,b.[logindate]) and c.name=b.name)) as grp
from tbl a
),m
as(
select [name],MIN([logindate]) as startdate,MAX(grp) as enddate
from t group by grp,name
)
select *,(DATEDIFF(DD,startdate,enddat
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
