--这样? select avg(s) from (select datepart(yy,tm),s=sum(p) from A where tm between @dtbegin and @dtend group by datepart(yy,tm))a
SELECT SUM(P) --总和 FROM A WHERE DatePart(Year,TM) Between 1951 AND 2008 AND ( DatePart(Month,TM)=1 OR (DatePart(Month,TM)=2 AND DatePart(Day,TM)=1 )
declare @i int set set @i=datediff(year,@dtBegin,@dtEnd)+1 --借用6楼的 SELECT SUM(P)/@i --平均值 FROM A WHERE DatePart(Year,TM) Between 1951 AND 2008 AND ( DatePart(Month,TM)=1 OR (DatePart(Month,TM)=2 AND DatePart(Day,TM)=1 )
@dtBegin和@dtEnd是输入参数,我说的那个时间只是一个实例,还有5楼你的也不行 tm between @dtbegin and @dtend 写死了,我要的是每一年的同期,要给他的年份进行处理 我不是说了想用动态SQL语句实现吗 declare @sql varchar(8000) declare @dtBegin datetime declare @dtEnd datetime declare @count intset @sql='sum(p) from A where tm between @dtBegin and @dtEnd' while(从1951年到2008年) { set @sql=@sql+'Or tm between and' 加一年 set @count=@coun+1 }
@dtBegin和@dtEnd是输入参数,我说的那个时间只是一个实例,还有5楼你的也不行 tm between @dtbegin and @dtend 写死了,我要的是每一年的同期,要给他的年份进行处理 我不是说了想用动态SQL语句实现吗 declare @sql varchar(8000) declare @dtBegin datetime declare @dtEnd datetime declare @count intset @sql='sum(p) from A where tm between @dtBegin and @dtEnd' while(从1951年到2008年) { set @sql=@sql+'Or tm between and' 加一年 set @count=@coun+1 }
樓主你只想取每一年的 n月n日 到 m月m日, 思路是把所有的“年”都忽略掉,就可以了select sum(P) from A where convert(varchar(05),TM,101) between @begin and @end
这个到是给了我一点启发,我这样写能得到结果,不过效率不高 select avg(s) as avgp from (select datepart(yy,dt) as year,s=sum(p) from AHSW2005.DBO.HY_DP_C where dt between dateadd(yy,datediff(yy,@dtbegin,dt),@dtBegin) and dateadd(yy,datediff(yy,@dtEnd,dt),@dtEnd) and STCD='50104200'and P is not null group by datepart(yy,dt)) a
不行吗?
convert(varchar(05), TM, 101 )
--这样?
select avg(s) from
(select datepart(yy,tm),s=sum(p) from A where tm between @dtbegin and @dtend
group by datepart(yy,tm))a
FROM A
WHERE DatePart(Year,TM) Between 1951 AND 2008
AND (
DatePart(Month,TM)=1
OR
(DatePart(Month,TM)=2 AND DatePart(Day,TM)=1
)
declare @i int
set set @i=datediff(year,@dtBegin,@dtEnd)+1
--借用6楼的
SELECT SUM(P)/@i --平均值
FROM A
WHERE DatePart(Year,TM) Between 1951 AND 2008
AND (
DatePart(Month,TM)=1
OR
(DatePart(Month,TM)=2 AND DatePart(Day,TM)=1
)
我不是说了想用动态SQL语句实现吗
declare @sql varchar(8000)
declare @dtBegin datetime
declare @dtEnd datetime
declare @count intset @sql='sum(p) from A where tm between @dtBegin and @dtEnd'
while(从1951年到2008年)
{
set @sql=@sql+'Or tm between and'
加一年
set @count=@coun+1
}
我不是说了想用动态SQL语句实现吗
declare @sql varchar(8000)
declare @dtBegin datetime
declare @dtEnd datetime
declare @count intset @sql='sum(p) from A where tm between @dtBegin and @dtEnd'
while(从1951年到2008年)
{
set @sql=@sql+'Or tm between and'
加一年
set @count=@coun+1
}
思路是把所有的“年”都忽略掉,就可以了select sum(P) from A
where convert(varchar(05),TM,101) between @begin and @end
这个到是给了我一点启发,我这样写能得到结果,不过效率不高
select avg(s) as avgp from
(select datepart(yy,dt) as year,s=sum(p) from AHSW2005.DBO.HY_DP_C where dt between dateadd(yy,datediff(yy,@dtbegin,dt),@dtBegin) and dateadd(yy,datediff(yy,@dtEnd,dt),@dtEnd) and STCD='50104200'and P is not null group by datepart(yy,dt)) a