select * from 表 a
where (
select count(distinct opdate) from 表 where datediff(day,a.opdate,opdate) between -9 and 9)>=10
where (
select count(distinct opdate) from 表 where datediff(day,a.opdate,opdate) between -9 and 9)>=10
where (
select distinct opdate from 表 where datediff(day,a.opdate,opdate) between -9 and 9)>=10不知是否正确,因为你的意思不是很明白
where (
select count(distinct opdate) from 表 where datediff(day,a.opdate,opdate) between -9 and 9)>=10
opdate
2004-06-01
2004-06-01
2004-06-02
2004-06-03
2004-06-05
2004-06-06
2004-06-06
2004-06-07
2004-06-08
2004-06-09
2004-06-09
2004-06-10
2004-06-11
2004-06-12
2004-06-17
2004-06-18如果要选择连续5天都有操作的话,查询结果如下:
2004-06-05
2004-06-06
2004-06-07
2004-06-09
2004-06-10
2004-06-05
2004-06-06
2004-06-07
2004-06-08
2004-06-09
declare @max_n as int
declare @sqlstr as varchar(8000)select @max_n=10
select @n=1
select @sqlstr=''while(@n<@max_n)
begin
select @sqlstr=@sqlstr + ' and exists (select opdate from 表 where opdate=dateadd(Day,'+ cast(@n as varchar(10)) +',A.opdate))'
select @n=@n+1
endselect @sqlstr='select opdate from 表 A where' + @sqlstr+' order by opdate'
select @sqlstr=Replace(@sqlstr,'where and','where')execute(@sqlstr)
select opdate from 表 A where
exists (select opdate from 表 where opdate=dateadd(Day,1,A.opdate))
and exists (select opdate from 表 where opdate=dateadd(Day,2,A.opdate))
and exists (select opdate from 表 where opdate=dateadd(Day,3,A.opdate))
and exists (select opdate from 表 where opdate=dateadd(Day,4,A.opdate))
and exists (select opdate from 表 where opdate=dateadd(Day,5,A.opdate))
and exists (select opdate from 表 where opdate=dateadd(Day,6,A.opdate))
and exists (select opdate from 表 where opdate=dateadd(Day,7,A.opdate))
and exists (select opdate from 表 where opdate=dateadd(Day,8,A.opdate))
and exists (select opdate from 表 where opdate=dateadd(Day,9,A.opdate))
order by opdate与本身的一天加起来刚好10天