求sql里查询连续五天大于某数值的语句2

[snail_t]

上次发了一个帖子，想要求sql里查询连续五天大于某数值的语句
帖子的地址是：http://bbs.csdn.net/topics/390703059
经过网友们的帮助，给出了以下语句进行查询
select *
from
(select min([年月日]) as bgdate, max([年月日]) as eddate, count(1) as maxdays
from
(select [年月日], datediff(dd, '1900-01-01', a.[年月日]) - (select count(1) from ##DateData58549 b where [日平均温度] >= 10 and datediff(dd, b.[年月日], a.[年月日]) >= 0) as maxdays
from ##DateData58549 a
where [日平均温度] >= 10) a
group by maxdays
having COUNT(1) >= 5
) b
order by bgdate
我不太看得懂a和b的指代关系，请问该找哪方面的文献？
另外，想要求出每年出现“连续五天大于某数值”的第一天，即一年只求一天，上述sql语句该怎么改呢？

[大力水手]

select datediff(dd,sn,date) ,count(1) as counts from ( select row_number() as sn,date,value from tb where value>'某个值' )tb group by datediff(dd,sn,date) having count(1)>=5 感觉你要的是这个逻辑。

[--小F--]

select 
 top 1 * 
from
 ( select
      min([年月日]) as bgdate, max([年月日]) as eddate, count(1) as maxdays
   from
   (select 
	  [年月日], datediff(dd, '1900-01-01', a.[年月日]) - (select count(1) from ##DateData58549 b where [日平均温度] >= 10 and datediff(dd, b.[年月日], a.[年月日]) >= 0) as  maxdays
   from
      ##DateData58549 a
   where
      [日平均温度] >= 10) as a
   group by
      maxdays
   having COUNT(1) >=  5
    ) as  b
 order by bgdate

只要一天的话可以TOP1就可以了 A和B都是因为在包含子查询以后需要加个别名 相当于AS A ,AS B这样

[snail_t]

引用 2 楼 fredrickhu 的回复:

select 
 top 1 * 
from
 ( select
      min([年月日]) as bgdate, max([年月日]) as eddate, count(1) as maxdays
   from
   (select 
	  [年月日], datediff(dd, '1900-01-01', a.[年月日]) - (select count(1) from ##DateData58549 b where [日平均温度] >= 10 and datediff(dd, b.[年月日], a.[年月日]) >= 0) as  maxdays
   from
      ##DateData58549 a
   where
      [日平均温度] >= 10) as a
   group by
      maxdays
   having COUNT(1) >=  5
    ) as  b
 order by bgdate

只要一天的话可以TOP1就可以了 A和B都是因为在包含子查询以后需要加个别名 相当于AS A ,AS B这样

如果加上top 1的话是对所有查询的记录里挑第一天吧？我想要的结果是每年的第一天，是不是加上top 1的同时还需要加上别的条件？