求一个sql语句,关于记录每天都出现

[banping]

有一个登陆日志表.现在要求统计根据条件统计出,每天都登陆的人.
连续N天都登陆的人.N是由外面条件社定的.
例子(如果一个人连续三天都登陆,第四天没有,第五天到第7天又每天都登陆,就要形成两条记录,表示出来,开始日期,结束日期,连续登陆次数)
中间间隔的天数,也要可以动态调整.
上面的例子是间隔>1天的,以后说不定要该成间隔大于3天的.
有没有什么Sql可以一句话就表达出来的.

表结构如下
guid,UserId,date.

[banping]

自己顶

[caixia615]

up

[playwarcraft]

表述不清楚,

舉些數據,
列出想要的結果

[冷箫轻笛]

--环境
create table users
(
guid int identity(1,1) not null,
userid int,
date datetime
)


insert into users(userid,date) select 1,'2006-01-01'
insert into users(userid,date) select 1,'2006-01-03'
insert into users(userid,date) select 1,'2006-01-06'
insert into users(userid,date) select 1,'2006-01-10'
insert into users(userid,date) select 1,'2006-01-11'
insert into users(userid,date) select 1,'2006-01-12'
insert into users(userid,date) select 1,'2006-01-13'
insert into users(userid,date) select 1,'2006-01-18'
insert into users(userid,date) select 1,'2006-01-19'
insert into users(userid,date) select 1,'2006-01-20'
insert into users(userid,date) select 1,'2006-01-25'
insert into users(userid,date) select 1,'2006-01-26'
insert into users(userid,date) select 1,'2006-01-28'
insert into users(userid,date) select 1,'2006-01-29'

--语句
select userid,[date] as begindate,[enddate] = (select min([date]) from users t1
where [date]>=t2.[date] and not exists(select 1 from users where userid = t1.userid and datediff(day,t1.[date],[date])=1)
)
from users t2
where not exists(select 1 from users where userid = t2.userid and datediff(day,[date],t2.[date])=1)

--结果
1 2006-01-01 00:00:00.000 2006-01-01 00:00:00.000
1 2006-01-03 00:00:00.000 2006-01-03 00:00:00.000
1 2006-01-06 00:00:00.000 2006-01-06 00:00:00.000
1 2006-01-10 00:00:00.000 2006-01-13 00:00:00.000
1 2006-01-18 00:00:00.000 2006-01-20 00:00:00.000
1 2006-01-25 00:00:00.000 2006-01-26 00:00:00.000
1 2006-01-28 00:00:00.000 2006-01-29 00:00:00.000

[banping]

数据举例：
userid date
1, 2006-10-01
1, 2006-10-01
2, 2006-10-01
1, 2006-10-02
1, 2006-10-06
1, 2006-10-07
1, 2006-10-07
1, 2006-10-08

想要的结果就是
userid begindate enddate count
1, 2006-10-01 2006-10-02 3
2, 2006-10-01 2006-10-01 1
1, 2006-10-06 2006-10-08 4

[冷箫轻笛]

--数据见楼上的楼上

--语句
select * ,[count] = (select count(1) from users where userid = t3.userid and date between begindate and enddate)
from
(
select userid,[date] as begindate,
[enddate] = (select min([date]) from users t1
where [date]>=t2.[date] and not exists(select 1 from users where userid = t1.userid and datediff(day,t1.[date],[date])=1)
)
from users t2
where not exists(select 1 from users where userid = t2.userid and datediff(day,[date],t2.[date])=1)
)t3

--结果
1 2006-01-01 00:00:00.000 2006-01-01 00:00:00.000 1
1 2006-01-03 00:00:00.000 2006-01-03 00:00:00.000 1
1 2006-01-06 00:00:00.000 2006-01-06 00:00:00.000 1
1 2006-01-10 00:00:00.000 2006-01-13 00:00:00.000 4
1 2006-01-18 00:00:00.000 2006-01-20 00:00:00.000 3
1 2006-01-25 00:00:00.000 2006-01-26 00:00:00.000 2
1 2006-01-28 00:00:00.000 2006-01-29 00:00:00.000 2

[banping]

谢谢,我来实验下

[marco08]

關注

[zxbyhcsdn]

冷箫轻笛 的统计出来好象也不太正确啊

[拓狼]

select distinct userid,convert(varchar(10),date,120) as date,0 num into #T from @users order by 1,2


declare @A int,@B int,@d datetime
update #T set
@B = case when @A=userid and @d=cast([date] as datetime)-1 and convert(varchar(7),@d,120)=convert(varchar(7),cast([date] as datetime),120) then @B+1 else 1 end,
num = @B,
@A = userid,@d=[date]


select userid,dateadd(day,-(num-1),date) as date1,date date2,num into #S from #T
select * from #S a where not exists(select 1 from #S b where a.userid=b.userid and a.date1=b.date1 and a.date2<b.date2)
drop table #T,#S

[proglovercn]

学习……

[冷箫轻笛]

什么地方不正确阿？

[冷箫轻笛]

晕倒！
沙子来~~~~？走了？
说说哪有问题先阿！