----生产年份临时表
select 1951 + number as dd into #1  from master.dbo.spt_values where type = 'p' and number <2008 - 1950
--------查询
select dd,max(p) from 
(
    select * from #1 a join 表A b on year(b.dt) = a.dd
    where     exists (select 1 from 表A where dt = b.dt - 1 )
    and exists (select 1 from 表A where dt = b.dt - 2 )
    and exists (select 1 from 表A where dt = b.dt + 1 )
    and exists (select 1 from 表A where dt = b.dt + 2 )
) T
group by dd

------解决方案--------------------
顶。。

------解决方案--------------------
楼上高手都帮顶了..小菜也顶!

------解决方案--------------------
探讨

SQL code----生产年份临时表
select 1951 + number as dd into #1  from master.dbo.spt_values where type = 'p' and number <2008 - 1950
--------查询
select dd,max(p) from 
(
    select * from #1 a join 表A b on year(b.dt) = a.dd
    where     exists (select 1 from 表A where dt = b.dt - 1 )
    and exists (select 1 from 表A where dt = b.dt - 2 )
    and exists (select 1 from 表A where dt = b.d…


------解决方案--------------------
帮顶

------解决方案--------------------
探讨

你这个求的并不是一年中三天Ｐ和的最大值，好象是最大的一天的Ｐ值


------解决方案--------------------
引用楼主 zhougang86 的帖子:

现有表Ａ(ID varchar(8),DT datetime,P float),primary key(ID,DT)  表记录数有３００万 
现在求一个ＩＤ每一年(表中有记录的年份，如ID=50100100 表Ａ中有记录的年份从1951-2008年)连续３天最大Ｐ值和(P为null不参与计算)． 
附Ａ表一些示例记录 
50100100 1951-1-1  2 
50100100 1951-1-2  3.5 
50100100 1951-1-3  null 
. 
. 
. 
50100100 1952-1-1  5 
50100100 1952-1-2  7 
50100100 1952-1-3  3.3 
. 
. 
…


------解决方案--------------------
是要这样的结果吗?
SQL code
create table tbA(id varchar(8),dt datetime,p float)
insert into tbA select '50100100','1951-1-1',2 union all
select '50100100','1951-1-2',3.5 union all
select '50100100','1951-1-3',4.2 union all
select '50100100','1951-1-4',null union all
select '50100100','1951-1-6',7.2 union all
select '50100100','1951-1-7',null union all
select '50100100','1951-1-8',10.5 union all
select '50100100','1952-1-2',5 union all
select '50100100','1952-1-3',7 union all
select '50100100','1952-1-4',3.3 union all
select '50100100','1952-1-5',6.5 union all
select '50100100','1952-1-6',1.2
go
select datepart(yy,a.dt) as [year],max(a.p+b.p+c.p) as sumP
 from tbA a inner join tbA b on a.id=b.id inner join tbA c on b.id=c.id
 where datediff(dd,a.dt,b.dt)=1 and datediff(dd,b.dt,c.dt)=1
   and datepart(yy,a.dt)=datepart(yy,b.dt) and datepart(yy,b.dt)=datepart(yy,c.dt)
   and not a.p is null and not b.p is null and not c.p is null
 group by datepart(yy,a.dt)
go
drop table tbA
/*
year        sumP
----------- ----------------------
1951        9.7
1952        16.8
*/

------解决方案--------------------