测试数据的生成，连续最大天数的求解，这里给出：（以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 @[email protected]+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
[email protected]
--function作用：[email protected]
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 @[email protected]
set @t=0
end
else set @[email protected]+1
set @[email protected]/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 allselect 'AA',21,31,'2012-02-14' union allselect 'AA',12,31,'2012-02-11' union allselect 'AA',-2,30,'2012-03-11' union allselect 'BB',-1,30,'2012-02-12' union allselect 'BB',90,31,'2012-02-11' union allselect 'BB',90,31,'2012-03-17' union allselect '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 agroup by [proname]/*proname xsqty xtqty lastxstime lastxstimeAA 33 -12 2012-02-14 2012-03-11BB 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 allselect 'a1','2011-1-3' union allselect 'a1','2011-1-4' union allselect 'a1','2011-1-7' union allselect 'a1','2011-1-12' union allselect 'a1','2011-1-13' union allselect 'a1','2011-1-16' union allselect 'a2','2011-1-7' union allselect 'a2','2011-1-8' union allselect 'a2','2011-1-10' union allselect 'a2','2011-1-11' union allselect 'a2','2011-1-13' union allselect '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.nameand not exists (select * from tbl cwhere c.[logindate]=dateadd(dd,1,b.[logindate]) and c.name=b.name)) as grpfrom tbl a),mas(select [name],MIN([logindate]) as startdate,MAX(grp) as enddatefrom t group by grp,name)select *,(DATEDIFF(DD,startdate,enddate)+1) as [days] from m a where (DATEDIFF(DD,startdate,enddate)+1) in(select max(DATEDIFF(DD,startdate,enddate)+1) from m bwhere a.name=b.name)/*name startdate enddate daysa1 2011-01-02 2011-01-04 3a2 2011-01-07 2011-01-08 2a2 2011-01-10 2011-01-11 2*/--你的整个方法都不怎么好