【100分】求根据时间分组的问题。

[叫我三三]

数据格式如下：
[sign] [date]
1 2012-01-01
1 2012-02-06

1 2012-05-03
1 2012-06-11

1 2012-09-01

2 2012-02-03
2 2012-04-19
2 2012-04-23


需求是，同一个标记，从最初的开始时间算起，
3个月以内同一个标记的算一组，下个日期依然按着个要求算，并标记出现次数

结果为
[sign] [date] [n]
1 2012-01-01 2
1 2012-05-03 2
1 2012-09-01 1
2 2012-02-03 3

求救，谢谢！

[叫我三三]

谢谢~ 经测试成功

[ForFumm]

写存储过程，用游标吧。

[叫我三三]

这个不用游标也能实现吧

[lzw_0736]

with a1 ([sign],[date]) as ( select 1,'2012-01-01' union all select 1,'2012-02-06' union all select 1,'2012-05-03' union all select 1,'2012-06-11' union all select 1,'2012-09-01' union all select 2,'2012-02-03' union all select 2,'2012-04-19' union all select 2,'2012-04-23' ) ,a2 as ( select [sign],min([date]) [date_min] from a1 group by [sign] ) ,a3 as ( select a1.*,ceiling(case when DATEDIFF(day,a2.[date_min],a1.[date])=0 then 1 else DATEDIFF(day,a2.[date_min],a1.[date])/90. end ) ms from a1 inner join a2 on a1.[sign]=a2.[sign] ) ,a4 as ( select [sign],ms,min(date) date,count(*) qty from a3 group by [sign],ms ) select [sign],date,qty from a4 order by 1,2

[闹铃]

对于每一行求出 与该行对应该id的最小日期相差的天数 ，如果天数除以90得到的数相同，则为一组

create table test
(
id int,
dt datetime
);
insert into test
select 1,'2012-01-01'
union all select 1, '2012-02-06'
union all select 1, '2012-05-03'
union all select 1, '2012-06-11'
union all select 1, '2012-09-01'
union all select 2, '2012-02-03'
union all select 2, '2012-04-19'
union all select 2, '2012-04-23'

;with cte as (select id,MIN(dt) as dt from test group by id)
select A.id,MIN(A.dt) as dt,COUNT(1) as N
from test as A
inner join cte as B on A.id=B.id
group by A.id,datediff(DAY,B.dt,A.dt)/90
order by A.id,dt;

drop table test;

id          dt                      N
----------- ----------------------- -----------
1           2012-01-01 00:00:00.000 2
1           2012-05-03 00:00:00.000 2
1           2012-09-01 00:00:00.000 1
2           2012-02-03 00:00:00.000 3

(4 行受影响)