http://bbs.csdn.net/topics/390499367
问题跟着上个帖子.
先谢谢lzw_0736和denghui_li帮我解决上面这个难题。
现在是
根据按照 同一个标记，从最初的开始时间算起，
3个月以内同一个标记的算一组，下个日期依然按着个要求算，并标记出现次数的需求。
把数据
[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
分组为：
[sign] [date] [n]
1 2012-01-01 2
1 2012-05-03 2
1 2012-09-01 1
2 2012-02-03 3
现在又添加了新的需求，需要根据分组的结果反查询到对应的数据。
我想来想去，只发现一个方法：
就把原有的数据变成
[sign] [date] [s_sign] [s_date] [s_n]
1 2012-01-01 1 2012-01-01 2
1 2012-02-06 1 2012-01-01 2
1 2012-05-03 1 2012-05-03 2
1 2012-06-11 1 2012-05-03 2
1 2012-09-01 1 2012-09-01 1
2 2012-02-03 2 2012-02-03 3
2 2012-04-19 2 2012-02-03 3
2 2012-04-23 2 2012-02-03 3
查询的统计结果的时候 Select distinct [s_sign] [s_date] [s_n]
详细的时候就查询 Select [sign] [date] from t1 where [s_sign], [s_date], [s_n]
但是我现在就是无法实现上面的格式，实验几个小时了
求救啊！！谢谢！
------解决方案--------------------
with t ([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'
)
,tGroup as
(
select [sign],min([date]) as [date]
from t
group by [sign]
)
select a.*,10000*a.[sign]+ datediff(DAY,b.[date],a.[date])/90 gn
from t a
join tGroup b
on a.[sign]=b.[sign]