请问如何实现计算连续几天某一变量大于给定值的次数

declare @N int, --天
    @temp int, --温度
    @whe varchar(20) --地点select @whe='南京',@N=5,@temp=30 --假设连续5天，温度为大于30度
select count(*) from table1 a
where wwhere=@whe and yyear between 1980 and 2006
and exists(select count(*) from table1 where wwhere=@whe and yyear between 1980 and 2006
               and convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday))
               between convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday))
                 and dateadd(day,@N-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday)))
                 and ttemperature>@temp  having count(*)>=@N )
本结果对多于@N天原连接记录会统计出多个结果，如要统计连续5天，如果有连续7天的情况会统计出3个连接5天的结果

declare @a table(a smalldatetime,wd int,value int)
insert @a select '2007-01-04',31,0
insert @a select '2007-01-05',33,0
insert @a select '2007-01-07',31,0
insert @a select '2007-01-08',31,0
insert @a select '2007-01-09',31,0
insert @a select '2007-01-10',31,0
insert @a select '2007-01-19',31,0
insert @a select '2007-02-01',31,0
insert @a select '2007-02-05',31,0
insert @a select '2007-02-14',31,0
insert @a select '2007-02-15',31,0
insert @a select '2007-02-16',31,0
insert @a select '2007-02-17',31,0
insert @a select '2007-02-18',31,0
insert @a select '2007-02-19',31,0declare @days int
declare @d smalldatetime,@step int,@p int
declare @t table(value int,days int)set @days=3
select @d='1900-01-01', @step=0update @a
set @p=case @d when a then 0 else 1 end, @d=dateadd(day,1,a),@step=value=@step+@pinsert @t select value,count(1) c from @a group by value having(count(1)>@days)select a,wd from @a where value in(select value from @t t where not exists(select 1 from @t where days>t.days))
--result
/*a                                                      wd
------------------------------------------------------ -----------
2007-02-14 00:00:00                                    31
2007-02-15 00:00:00                                    31
2007-02-16 00:00:00                                    31
2007-02-17 00:00:00                                    31
2007-02-18 00:00:00                                    31
2007-02-19 00:00:00                                    31
*/

按小陈的方法提示datetime数据转换出错，如果将'_'改成'/'不提示出错，但结果不对，什么原因呢

如果测试数据
insert @a select '2007-02-20',29,0 有温度小于30度
则result也出现'2007-02-20',29 温度在你的语句中用什么来控制。
还有你的思路能否用语言简单描绘一下，万分谢谢!

昨天已回贴，不知为何不见。
declare @N int,--天数
     @temp int,--温度
     @whe varchar(20) --地点
select @N=5,@temp=30,@whe='南京'
select count(*) from table1 a where wwhere=@whe and yyear between 1980 and 2006
    and (select count(*) from table1 where wwhere=@whe and yyear between 1980 and 2006 and ttemperature>@temp
  and convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday)) between
              convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday))
and dateadd(day,@n-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday))))>@N

小陈的方式我测试怎么不对
发现什么问题，我是假设每个地点每天只有一条记录，如果是这样，结果不对，调整一个>@n,将其改为()>=@N应该无问题，
如果是每个地点每天多于一条记录，那要先把多余的记录去掉。
假设yyear,mmonth,dday,ttemperature字段都为整数类型

我给的测试数据是假设你已经把地址为南京的，温度超过30度的，年度为某一区域的已经过滤出来的数据
得到的结果是温度连续超过30度且大于3天的最大的连续天数，是6天由于不智道你要啥结果，我算出的是连续超过30度且连续天数最大的记录，如果你要算别的，可以看update @a这一句后表@a的记录就清楚了

按小陈最新提供的方法即
declare @N int,--天数
     @temp int,--温度
     @whe varchar(20) --地点
select @N=5,@temp=30,@whe='南京'
select count(*) from table1 a where wwhere=@whe and yyear between 1980 and 2006
    and (select count(*) from table1 where wwhere=@whe and yyear between 1980 and 2006 and ttemperature>@temp
          and convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday)) between
              convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday))
             and dateadd(day,@n-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday))))>@N进行测试结果不对，结果不是0就是我的整个表的行数，当改变@temp的初始值如35等，结果还是如此
简单构造一下表
wwhere　yyear  mmonth   dday　ttemperature
南京　　　1980　8　　　　　1　　　25
南京　　　1980　8　　　　　2　　　27
南京　　　1980　8　　　　　3　　　31
南京　　　1980　8　　　　　4　　　32
南京　　　1980　8　　　　　5　　　33
南京　　　1980　8　　　　　6　　　18
南京　　　1980　8　　　　　7　　　25
南京　　　1980　8　　　　　8　　　34
南京　　　1980　8　　　　　9　　　31
南京　　　1980　8　　　　　10　　　33
南京　　　1980　8　　　　　11　　　35
南京　　　1980　8　　　　　12　　　27
南京　　　1980　8　　　　　14　　　32
南京　　　1980　8　　　　　15　　　33
南京　　　1980　8　　　　　16　　　18
取set ＠N＝2,@temp=30 按小陈的方法不对
按树上的鸟儿的方法也不对，请问为什么，帮帮忙，谢谢！
　　　　
　

set nocount on
declare @t table(wwhere varchar(4),yyear char(4),mmonth varchar(2),dday varchar(2),ttemperature int)
insert @t select '南京','1980','8','1',25
insert @t select '南京','1980','8','2',27
insert @t select '南京','1980','8','3',31
insert @t select '南京','1980','8','4',32
insert @t select '南京','1980','8','5',33
insert @t select '南京','1980','8','6',18
insert @t select '南京','1980','8','7',25
insert @t select '南京','1980','8','8',34
insert @t select '南京','1980','8','9',31
insert @t select '南京','1980','8','10',33
insert @t select '南京','1980','8','11',35
insert @t select '南京','1980','8','12',27
insert @t select '南京','1980','8','14',32
insert @t select '南京','1980','8','15',33
insert @t select '南京','1980','8','16',18
declare @M int
declare @temp int
set @M = 3
set @temp = 30select  A.wwhere ,a.mmonth,A.dday,max(a.ttemperature) as ttemperature
from @t A,@t B,
(select 1 as N
union all select 2
union all select 0
) C
where A.wwhere=B.wwhere and A.mmonth=B.mmonth
and abs(cast(B.dday as int) - cast(A.dday as int)) = N
and a.ttemperature > @temp and b.ttemperature > @temp
group by A.wwhere,a.mmonth,A.dday
having(count(*)>=@M)
/*
wwhere mmonth dday        ttemperature
------ ------ ----------- ------------
南京     8      3           31
南京     8      4           32
南京     8      5           33
南京     8      8           34
南京     8      9           31
南京     8      10          33
南京     8      11          35
*/

非常感谢happyflystone，明天我就用你的方法去测试我那些表。
因为我要处理的表很大，数据很多，你现在给出的结果还不能完全解决我的问题。我希望得到的最终结果是
1、计算最大连续日数（在现在例子中是4即从8号到11号）。
2、计算任意连续N天的总次数。
3、计算最多连续日数，即连续几天的频率最多，如果连续3天为20次，连续4天为15次，连续5天为18次，我们就认为连续3天的频率
最多。
还望出手相救，万分感谢！

非常感谢happyflystone，明天我就用你的方法去测试我那些表。
因为我要处理的表很大，数据很多，你现在给出的结果还不能完全解决我的问题。我希望得到的最终结果是
1、计算最大连续日数（在现在例子中是4即从8号到11号）。
2、计算任意连续N天的总次数。
3、计算最多连续日数，即连续几天的频率最多，如果连续3天为20次，连续4天为15次，连续5天为18次，我们就认为连续3天的频率
最多。
还望出手相救，万分感谢！
---给点数据，并说最终的结果　来，
你的给的数据1－12　全中连续的呀，为什么　是8－11？N天的次数是什么意思

set nocount on
declare @test table (wwhere varchar(20),yyear int,mmonth int,dday int,ttemperature int)insert into @test values('南京',1980,8,1,25)
insert into @test values('南京',1980,8,2,27)
insert into @test values('南京',1980,8,3,31)
insert into @test values('南京',1980,8,4,32)
insert into @test values('南京',1980,8,5,33)
insert into @test values('南京',1980,8,6,18)
insert into @test values('南京',1980,8,7,25)
insert into @test values('南京',1980,8,8,34)
insert into @test values('南京',1980,8,9,31)
insert into @test values('南京',1980,8,10,33)
insert into @test values('南京',1980,8,11,35)
insert into @test values('南京',1980,8,12,27)
insert into @test values('南京',1980,8,14,32)
insert into @test values('南京',1980,8,15,33)
insert into @test values('南京',1980,8,16,18)
declare   @N   int,--天数
          @temp   int,--温度
          @whe   varchar(20)   --地点
select   @N=3,@temp=30,@whe='南京'
select   count(*)   from   @test   a   where   wwhere=@whe   and   yyear   between   1980   and   2006
        and   (select   count(*)   from   @test   where   wwhere=@whe   and   yyear   between   1980   and   2006   and   ttemperature> @temp
              and   convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)   between
             convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)
           and   dateadd(day,@n-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)))> =@N
无什么问题,得出的结果是次数

你是否还要显示哪些天的数据.我计算的是次数.
要快速查询,最好再加一datetime字段,把年月日字段的数据先转为datetime类型.
不能用dday直接比较,因对夸月的日期无效.如1980-8-30到1980-9-2.2、计算任意连续N天的总次数。
根据以上提供的方法查询得到3、计算最多连续日数，即连续几天的频率最多，如果连续3天为20次，连续4天为15次，连续5天为18次，我们就认为连续3天的频率
与2方法相同,给出不同的天数,根据2算出次数,再比较即可.

对于
1、计算最大连续日数（在现在例子中是4即从8号到11号）。
可按以下思路解决,
先查询出合条件的记录 where wwhere='地点' and 日期范围 and 温度>给定的温度
得出结果再与一简单的单字段表
此表单字段,保存1,2,3,4,5...到你想要的最大记录极限
结果与此表相连接,计算出最大的连续天数.

按无枪狙击手的方法适合列举哪年哪月哪日的具体情况，但测试时算法有问题，例如
再在原数据中插入
insert @t select '南京','1982','8','4',35
insert @t select '南京','1982','8','5',33
insert @t select '南京','1982','8','6',18
insert @t select '南京','1983','8','7',25
insert @t select '南京','1983','8','8',34
insert @t select '南京','1984','8','9',31
insert @t select '南京','1985','8','10',33
insert @t select '南京','1983','8','8',35
就会发现结果不对，请教无枪狙击手如何解决，并请您大概讲讲您的算法，我知识浅薄

谢谢小陈发表于：2007-12-26 23:58的方法，您的方法适合实现计算连续几天某一变量大于给定值的次数，我在测试时没有区分a.yyear和yyear,所以结果错了。还想请教您的是：
1、请问你还有办法显示哪些天的数据吗(符合连续几天某一变量大于给定值的次数)？
2、如果我在原始数据中地点wwhere很多，如有北京、上海等100多个地方，能否用一个表来显示出所有的地方连续几天某一变量大于给定值的次数，而非每次手工改地名，然后再加入一个表中？或者说在SQL中如何用数组来存放地名？
谢谢

set nocount on
declare @test table (wwhere varchar(20),yyear int,mmonth int,dday int,ttemperature int)insert into @test values('南京',1980,7,27,25)
insert into @test values('南京',1980,7,28,27)
insert into @test values('南京',1980,7,29,31)
insert into @test values('南京',1980,7,30,32)
insert into @test values('南京',1980,7,31,33)
insert into @test values('南京',1980,8,1,25)
insert into @test values('南京',1980,8,2,27)
insert into @test values('南京',1980,8,3,31)
insert into @test values('南京',1980,8,4,32)
insert into @test values('南京',1980,8,5,33)
insert into @test values('南京',1980,8,6,18)
insert into @test values('南京',1980,8,7,25)
insert into @test values('南京',1980,8,8,34)
insert into @test values('南京',1980,8,9,31)
insert into @test values('南京',1980,8,10,33)
insert into @test values('南京',1980,8,11,35)
insert into @test values('南京',1980,8,12,27)
insert into @test values('南京',1980,8,14,32)
insert into @test values('南京',1980,8,15,33)
insert into @test values('南京',1980,8,16,18) insert into @test values('上海',1982,8,1,25)
insert into @test values('上海',1982,8,2,27)
insert into @test values('上海',1982,8,3,31)
insert into @test values('上海',1982,8,4,32)
insert into @test values('上海',1982,8,5,33)
insert into @test values('上海',1982,8,6,18)
insert into @test values('上海',1982,8,7,25)
insert into @test values('上海',1982,8,8,34)
insert into @test values('上海',1982,8,9,31)
insert into @test values('上海',1982,8,10,33)
insert into @test values('上海',1982,8,11,35)
insert into @test values('上海',1982,8,12,27)
insert into @test values('上海',1982,8,14,32)
insert into @test values('上海',1982,8,15,33)
insert into @test values('上海',1982,8,16,18)
declare   @N   int,--天数
          @temp   int,--温度
          @whe   varchar(20)   --地点
select   @N=3,@temp=30,@whe='南京'
--1、请问你还有办法显示哪些天的数据吗(符合连续几天某一变量大于给定值的次数)？
--改一改
select   *   from   @test   a   where   wwhere=@whe   and   yyear   between   1980   and   2006
        and   (select   count(*)   from   @test   where   wwhere=@whe   and   yyear   between   1980   and   2006   and   ttemperature> @temp
              and   convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)   between
             convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)
           and   dateadd(day,@n-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)))> =@N --以上得到的是前面@N天连续>@temp的日期,将其与原表连接,加上条件即可得到结果
select distinct t1.* from @test t1,(select   *   from   @test   a   where   wwhere=@whe   and   yyear   between   1980   and   2006
        and   (select   count(*)   from   @test   where   wwhere=@whe   and   yyear   between   1980   and   2006   and   ttemperature> @temp
              and   convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)   between
             convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)
           and   dateadd(day,@n-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)))> =@N ) t2
where t1.wwhere=@whe and convert(datetime,convert(varchar,t1.yyear)+'-'+convert(varchar,t1.mmonth)+'-'+convert(varchar,t1.dday),120)
      between convert(datetime,convert(varchar,t2.yyear)+'-'+convert(varchar,t2.mmonth)+'-'+convert(varchar,t2.dday),120)
        and dateadd(day,@n-1,convert(datetime,convert(varchar,t2.yyear)+'-'+convert(varchar,t2.mmonth)+'-'+convert(varchar,t2.dday),120))--2、如果我在原始数据中地点wwhere很多，如有北京、上海等100多个地方，能否用一个表来显示出所有的地方连续几天某一变量大于给定值的次数，
--  而非每次手工改地名，然后再加入一个表中？或者说在SQL中如何用数组来存放地名？ select  wwhere,count(*) 次数  from   @test   a   where   yyear   between   1980   and   2006
        and   (select   count(*)   from   @test   where   wwhere=a.wwhere and   yyear   between   1980   and   2006   and   ttemperature> @temp
              and   convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)   between
             convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)
           and   dateadd(day,@n-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)))> =@N
group by wwhere
--1、计算最大连续日数（在现在例子中是4即从8号到11号）。
declare @tableno table (no int)
insert into @tableno values (2)
insert into @tableno values (3)
insert into @tableno values (4)
insert into @tableno values (5)
insert into @tableno values (6)
insert into @tableno values (7)
insert into @tableno values (8)
insert into @tableno values (9)
insert into @tableno values (10)select wwhere,yyear,mmonth,dday,max(no) from @test a,@tableno b
where a.yyear between 1980 and 2006 and
(select   count(*)   from   @test   where   wwhere=a.wwhere and   yyear   between   1980   and   2006   and   ttemperature> @temp
              and   convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)   between
             convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)
           and   dateadd(day,b.no-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)))> =b.no
group by wwhere,yyear,mmonth,dday
--以上得到的是各日期的最大连续温度>@temp的天数
--将其保存后再查询,即可得最大连续日数（在现在例子中是4即从8号到11号）
if object_id('tempdb..#tmp') is not null drop table #tmp --如果临时表#tmp存在即删除它select wwhere,yyear,mmonth,dday,max(no) num into #tmp from @test a,@tableno b
where a.yyear between 1980 and 2006 and
(select   count(*)   from   @test   where   wwhere=a.wwhere and   yyear   between   1980   and   2006   and   ttemperature> @temp
              and   convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)   between
             convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)
           and   dateadd(day,b.no-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)))> =b.no
group by wwhere,yyear,mmonth,dday
   --查询#tmp
select *,convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120),'到',dateadd(day,num-1,convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)),'温度>@temp的连接天数最大' from #tmp a where not exists(select * from #tmp where wwhere=a.wwhere and num>a.num)

    --用以上查询即可得到结果,假如结果的连续天数最大的结果=10 即表@tableno的最大值,则连续的天数有可能>10 这时可增加@tableno的记录数.
    --或将表@tableno的记录改为11-20,再用以上相同方法,即可求得最大值,如还不行,循环使用以上方法,即可求得最大天数

非常感谢小陈，按你的方法问题得到了解决，但还出现一个新情况：我用循环计算N＝2,3,.....20天的各个地方的连续次数，并把结果输入不同(@flname)的文件中，出错，具体如下：
declare   @N   int,--天数
          @temp   int,--温度
          @whe   varchar(20) , --地点
　　　　　@sql varchar(1000),
         @flname varchar(1000)
select   @N=2,@temp=30
while @n<=20
begin
set @flname='times' + cast(@N as varchar)
set @sql='select  wwhere ,@n as times ,count(*) as sumtimes   from   @test   a  into '+@flname+' where   yyear   between   1980   and   2006
        and   (select   count(*)   from   @test   where yyear   between   1980   and   2006   and   ttemperature> @temp
              and   convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)   between
             convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)
           and   dateadd(day,@n-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)))> =@N
group by wwhere'
exec(sql)
set @n=@n+1
end 提示错误大概意思是数据类型运算，减号'-'有误，当我去除 where   yyear   between   1980   and   2006
        and   (select   count(*)   from   @test   where yyear   between   1980   and   2006   and   ttemperature> @temp
              and   convert(datetime,convert(varchar,yyear)+'-'+convert(varchar,mmonth)+'-'+convert(varchar,dday),120)   between
             convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)
           and   dateadd(day,@n-1,convert(datetime,convert(varchar,a.yyear)+'-'+convert(varchar,a.mmonth)+'-'+convert(varchar,a.dday),120)))> =@N
这部分，就不出错
请问如何修改这个程序。
谢谢先！

set nocount on
declare @t table(wwhere varchar(4),yyear char(4),mmonth varchar(2),dday varchar(2),ttemperature int)
insert @t select '南京','1980','8','1',25
insert @t select '南京','1980','8','2',27
insert @t select '南京','1980','8','3',31
insert @t select '南京','1980','8','4',32
insert @t select '南京','1980','8','5',33
insert @t select '南京','1980','8','6',18
insert @t select '南京','1980','8','7',25
insert @t select '南京','1980','8','8',34
insert @t select '南京','1980','8','9',31
insert @t select '南京','1980','8','10',33
insert @t select '南京','1980','8','11',35
insert @t select '南京','1980','8','12',27
insert @t select '南京','1980','8','14',32
insert @t select '南京','1980','8','15',33
insert @t select '南京','1980','8','16',18 insert   @t   select   '南京','1982','8','4',35
insert   @t   select   '南京','1982','8','5',33
insert   @t   select   '南京','1982','8','6',18
insert   @t   select   '南京','1983','8','7',25
insert   @t   select   '南京','1983','8','8',34
insert   @t   select   '南京','1984','8','9',31
insert   @t   select   '南京','1985','8','10',33
insert   @t   select   '南京','1983','8','8',35
declare @M int
declare @temp int
set @M = 3
set @temp = 30select  A.wwhere,a.yyear ,a.mmonth,A.dday,max(a.ttemperature) as ttemperature
from @t A,@t B,
(select 1 as N
union all select 2
union all select 0
) C
where A.wwhere=B.wwhere and A.mmonth=B.mmonth
and abs(cast(B.dday as int) - cast(A.dday as int)) = N
and a.ttemperature > @temp and b.ttemperature > @temp
group by A.wwhere,a.yyear,a.mmonth,A.dday
having(count(*)>=@M)
/*
wwhere yyear mmonth dday ttemperature
------ ----- ------ ---- ------------
南京     1980  8      10   33
南京     1980  8      11   35
南京     1980  8      3    31
南京     1980  8      4    32
南京     1980  8      5    33
南京     1980  8      8    34
南京     1980  8      9    31
南京     1982  8      4    35
南京     1982  8      5    33
南京     1983  8      8    35
南京     1984  8      9    31
南京     1985  8      10   33
*/
这个结果有问题吗？

你有重复　记录？
我　修改部分数据set nocount on
declare @t table(wwhere varchar(4),yyear char(4),mmonth varchar(2),dday varchar(2),ttemperature int)
insert @t select '南京','1980','8','1',25
insert @t select '南京','1980','8','2',27
insert @t select '南京','1980','8','3',31
insert @t select '南京','1980','8','4',32
insert @t select '南京','1980','8','5',33
insert @t select '南京','1980','8','6',18
insert @t select '南京','1980','8','7',25
insert @t select '南京','1980','8','8',34
insert @t select '南京','1980','8','9',31
insert @t select '南京','1980','8','10',33
insert @t select '南京','1980','8','11',35
insert @t select '南京','1980','8','12',27
insert @t select '南京','1980','8','14',32
insert @t select '南京','1980','8','15',33
insert @t select '南京','1980','8','16',18 insert   @t   select   '南京','1982','8','4',35
insert   @t   select   '南京','1982','8','5',33
insert   @t   select   '南京','1982','8','6',18
insert   @t   select   '南京','1983','8','6',35
insert   @t   select   '南京','1983','8','8',34
insert   @t   select   '南京','1984','8','9',31
insert   @t   select   '南京','1985','8','10',33
insert   @t   select   '南京','1983','8','7',35
declare @M int
declare @temp int
set @M = 3
set @temp = 30select  A.wwhere,a.yyear ,a.mmonth,A.dday,max(a.ttemperature) as ttemperature
from @t A,@t B,
(select 1 as N
union all select 2
union all select 0
) C
where A.wwhere=B.wwhere and a.yyear = b.yyear and A.mmonth=B.mmonth
and abs(datediff(d,a.yyear+'-'+a.mmonth+ '-'+ a.dday,b.yyear+'-'+b.mmonth+ '-'+ b.dday)) = N
and a.ttemperature > @temp and b.ttemperature > @temp
group by A.wwhere,a.yyear,a.mmonth,A.dday
having(count(*)>=@M)
order by a.yyear,a.mmonth,cast(a.dday as int)
/*
wwhere yyear mmonth dday ttemperature
------ ----- ------ ---- ------------
南京     1980  8      10   33
南京     1980  8      11   35
南京     1980  8      3    31
南京     1980  8      4    32
南京     1980  8      5    33
南京     1980  8      8    34
南京     1980  8      9    31
南京     1982  8      4    35
南京     1982  8      5    33
南京     1983  8      8    35
南京     1984  8      9    31
南京     1985  8      10   33
*/

set nocount on
declare @t table(wwhere varchar(4),yyear char(4),mmonth varchar(2),dday varchar(2),ttemperature int)
insert @t select '南京','1980','8','1',25
insert @t select '南京','1980','8','2',27
insert @t select '南京','1980','8','3',31
insert @t select '南京','1980','8','4',32
insert @t select '南京','1980','8','5',33
insert @t select '南京','1980','8','6',18
insert @t select '南京','1980','8','7',25
insert @t select '南京','1980','8','8',34
insert @t select '南京','1980','8','9',31
insert @t select '南京','1980','8','10',33
insert @t select '南京','1980','8','11',35
insert @t select '南京','1980','8','12',27
insert @t select '南京','1980','8','14',32
insert @t select '南京','1980','8','15',33
insert @t select '南京','1980','8','16',18 insert   @t   select   '南京','1982','8','4',35
insert   @t   select   '南京','1982','8','5',33
insert   @t   select   '南京','1982','8','6',18
insert   @t   select   '南京','1983','8','6',35
insert   @t   select   '南京','1983','8','8',34
insert   @t   select   '南京','1984','8','9',31
insert   @t   select   '南京','1985','8','10',33
insert   @t   select   '南京','1983','8','7',35
declare @M int
declare @temp int
set @M = 3
set @temp = 30select  A.wwhere,a.yyear ,a.mmonth,A.dday,max(a.ttemperature) as ttemperature
from @t A,@t B,
(select 1 as N
union all select 2
union all select 0
) C
where A.wwhere=B.wwhere and a.yyear = b.yyear and A.mmonth=B.mmonth
and abs(datediff(d,a.yyear+'-'+a.mmonth+ '-'+ a.dday,b.yyear+'-'+b.mmonth+ '-'+ b.dday)) = N
and a.ttemperature > @temp and b.ttemperature > @temp
group by A.wwhere,a.yyear,a.mmonth,A.dday
having(count(*)>=@M)
order by a.yyear,a.mmonth,cast(a.dday as int)
/*
wwhere yyear mmonth dday ttemperature
------ ----- ------ ---- ------------
南京     1980  8      3    31
南京     1980  8      4    32
南京     1980  8      5    33
南京     1980  8      8    34
南京     1980  8      9    31
南京     1980  8      10   33
南京     1980  8      11   35
南京     1983  8      6    35
南京     1983  8      7    35
南京     1983  8      8    34*/

主要是这部分
select 1 as N
union all select 2
union all select 0
) C
什么意思

调试易

请问如何实现计算连续几天某一变量大于给定值的次数

解决方案 »