查询联系N天记录

[luofenghen]

no part date
1 AA 2011-03-15
2 AA 2011-03-16
3 bb 2011-03-15
4 bb 2011-03-17
5 AA 2011-03-18

查询 连续2天 的记录

结果为
no part date
1 AA 2011-03-15
2 AA 2011-03-16


请问 该如何写sql

[luofenghen]

看下了 试了下 想了下
发现有点问题
这样得到的是连续一天的
那我要得到连续3天 N天的呢.....
no part date
1 AA 2011-03-15
2 AA 2011-03-16
3 AA 2011-03-17
4 AA 2011-03-18

[luofenghen]

如上 所说 查询得到连续3天的记录 应该是

no part date
1 AA 2011-03-15
2 AA 2011-03-16
3 AA 2011-03-17
4 AA 2011-03-18
查询得到连续4天的记录 应该是

no part date
1 AA 2011-03-15
2 AA 2011-03-16
3 AA 2011-03-17
4 AA 2011-03-18
查询得到连续5天的记录 应该是
为空

[AcHerat]

declare @date datetime
set @date = '2011-03-16'  --查询起始日期

select *
from tb
where date between @date and dateadd(dd,2,@date)

[--小F--]

select
  a.*
from
  tb a,tb b
where
  a.part=b.part
and
  datediff(dd.a.date,b.date)=1

[天-笑]

select t1.*,t2.date from t t1 inner join t t2 
on t1.part = t2.part and t1.date = dateadd(d,-1,t2.date)

[快溜]

selec * from tb a
where exists(select 1 from tb where (no=a.no+1 and datediff(date,a.date)=1)
                                   or(no=a.no-1 and datediff(date,a.date)=-1))

[叶子]

declare @t table (no int,part varchar(2),date datetime)
insert into @t
select 1,'AA','2011-03-15' union all
select 2,'AA','2011-03-16' union all
select 3,'bb','2011-03-15' union all
select 4,'bb','2011-03-17' union ALL
select 5,'bb','2011-03-18' union ALL -- 我添加的数据，方便测试
select 6,'AA','2011-03-18'

;WITH maco AS(
SELECT a.NO AS ano,a.part AS apart,a.date AS adate,
b.NO AS bno,b.part AS bpart,b.date AS bdate FROM @t a
LEFT JOIN @t b
ON a.part=b.part AND a.date=b.date-1)

SELECT ano,apart,adate FROM maco WHERE bno IS NOT NULL
UNION ALL
SELECT bno,bpart,bdate FROM maco WHERE bno IS NOT NULL
ORDER BY adate
/*
1	AA	2011-03-15 00:00:00.000
2	AA	2011-03-16 00:00:00.000
4	bb	2011-03-17 00:00:00.000
5	bb	2011-03-18 00:00:00.000
*/

[--小F--]

select a.* from tb a,tb b where a.part=b.part and datediff(dd.a.date,b.date)=1
union all
select b.* from tb a,tb b where a.part=b.part and datediff(dd.a.date,b.date)=1