下面这条语句可以实现从表1中统计历年'03-01' 到 '04-30'间要素t的平均值
其中dateday是日期型数据。select 站号,year(dateday)[yyyy],avg(t)[AVG_T]
from 表1
where SUBSTRING(CONVERT(VARCHAR(10),dateday,120),6,5) between '03-01' and '04-30'
and 站号='56386'
group by year(dateday),站号
order by yyyy
我现在想要实现跨年的统计该怎么做?
如统计历年冬季t的平均值
1951-12-1~1952-2-28 的avg(t)
1952-12-1~1953-2-28....
1953-12-1~1954-2-28....……
以此类推到当前年份
其中dateday是日期型数据。select 站号,year(dateday)[yyyy],avg(t)[AVG_T]
from 表1
where SUBSTRING(CONVERT(VARCHAR(10),dateday,120),6,5) between '03-01' and '04-30'
and 站号='56386'
group by year(dateday),站号
order by yyyy
我现在想要实现跨年的统计该怎么做?
如统计历年冬季t的平均值
1951-12-1~1952-2-28 的avg(t)
1952-12-1~1953-2-28....
1953-12-1~1954-2-28....……
以此类推到当前年份
select 站号,year(dateday)[yyyy],avg(t)[AVG_T]
from 表1
where convert(varchar(10),dateday,120) between '1951-12-1' and '1952-2-28'
and 站号='56386'
group by year(dateday),站号
order by yyyy
站号,
avg(t)[AVG_T]
from 表1
where CONVERT(VARCHAR(10),dateday,120) between ltrim(year(dateday))+'-12-01' and ltrim(year(dateday)+1)+'-02-28'
and 站号='56386'
group by ltrim(year(dateday))+'-12-01~' +ltrim(year(dateday)+1)+'-02-28',站号
order by ltrim(year(dateday))+'-12-01~' +ltrim(year(dateday)+1)+'-02-28'
declare @bm int,--起始月份
@em int --截止月份
select @bm=3,@em=4 --赋值
--查询
select 站号,year(dateday)[yyyy],avg(t)[AVG_T]
from 表1
where year(dateday)*100+month(dateday) between year(dateday)*100+@bm and case when @bm<@em then year(dateday)*100+@em else (year(dateday)+1)*100+@em end
and 站号='56386'
group by year(dateday),站号
order by yyyy
decalre @beginDate char(4),@EndDate char(4)
select @beginDate ='1201',@EndDate = '0228'
select 站号,year(dateday)[yyyy],avg(t)[AVG_T]
from 表1
where
dateday between cast(cast(year(dateday) as varchar)+@beginDate as datetime)
and cast(cast(year(dateday)+1 as varchar)+@EndDate as datetime)
and 站号='56386'
group by year(dateday),站号
declare @beginDate char(4),@EndDate char(4)
select @beginDate ='1201',@EndDate = '0228'
select year(dateday)[yyyy],avg(t)[AVG_T]
from table_pqs
where
dateday between cast(cast(year(dateday) as varchar)+@beginDate as datetime)
and cast(cast(year(dateday)+1 as varchar)+@EndDate as datetime)
and 站号='56386'
group by year(dateday)
实际出来的结果等同于如果代码产生的结果:select 站号,year(dateday)[yyyy],avg(t)[AVG_T]
from 表1
where SUBSTRING(CONVERT(VARCHAR(10),dateday,120),6,5) between '12-01' and '12-31'
and 站号='56386'
group by year(dateday),站号
order by yyyy即等同于计算'12-01' ~'12-31'的平均值,而没有包括次年1、2月的数值
不解SQL怎么会这样……
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
用这个连接数据库
Data Source=219.89.2.162;Initial Catalog=LSNQZX;User ID=sql_temp;Password=sql_temp123
表1名: qxcyday
站号字段名:iiiii
时间: dateday
温度: t在线等
declare @tmp table (fdate datetime)
插入你所要统计的年份日期,如要统计 80-90每二年的数据就插入1980-1-1,1982-1-1,...1990-1-1
以此表为基表,用此表与你要统计的表连接,分组统计
如统计历年冬季t的平均值
1951-12-1~1952-2-28 的avg(t)
1952-12-1~1953-2-28....
1953-12-1~1954-2-28....楼主的意思是不是要把次年01-01到02-28之间的数据算到上一年中去?
我想要计算历年每年12月1日至下一年2月28的T平均值
如1951年,就是计算1951-12-1到1952-2-28的平均值
1952年……………1952-12-1到1952-2-28
……………………但现在上面几种方面计算出来的结果都相当于只计算了当年12-1到12-31的平均值,而自动忽略了第2年1-1到2-28的值,我估计是group by的问题,但还没找到解决办法
select yyyy,avg(avg_t) as avg_t from
(select yyyy=case when month(dateday)<=2 then year(dateday)-1 else year(dateday) end ,avg_t
from #t where [站号]='001') t
group by yyyy
'如果不想把月份写死,可以定义变量declare @n int
set @n=1select yyyy,avg(avg_t) as avg_t from
(select yyyy=case when month(dateday)<=@n then year(dateday)-1 else year(dateday) end ,avg_t
from #t where [站号]='001') t
group by yyyy
CREATE TABLE [dbo].[Table_Pqs](
[dateday] [datetime] NOT NULL,
[t] [int] NOT NULL
) ON [PRIMARY]
goinsert into [dbo].[Table_Pqs]
select '2009-1-3',2
union all
select '2008-2-4',4
union all
select '2009-4-5',8godeclare @minY int,@maxY int
select @minY=year(min(dateday)),@maxY=year(max(dateday)) from table_pqs
--select @minY,@maxY
declare @table table(y int identity(0,1),yearD int,beginD char(10),endD char(10))
insert into @table (yearD)
select top 100 null from sys.objects a,sys.objects b--select * from @table
declare @beginDate char(6),@EndDate char(6)
declare @kn int --是否隔年,就是计算开始、截止日期年间隔, 隔一年为1 ,隔2年为2,当年为0
select @beginDate ='-01-01',@EndDate = '-02-28',@kn=1
update @table set yearD=@minY+y,beginD=cast(@minY+y as varchar)+@beginDate
,endD= cast(@minY+y+@kn as varchar)+@EndDate
select * from @tableselect yearD,SUM(T) from @table a inner join table_pqs b
ON dateDay between beginD and endD
group by yearD
--resualt
2008 6
2009 10
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,PiaoQingSong>
-- Create date: <Create Date,2010-5-25,>
-- Description: <Description,,>
-- =============================================
aLTER PROCEDURE dbo.ProGetData
@beginDate char(6), --开始日期(只要月日部分),格式 '-03-12'
@EndDate char(6), --截止日期(只要月日部分),格式 '-12-06'
@kn int --开始、截止日期的年份间隔
AS
BEGIN SET NOCOUNT ON; declare @minY int,@maxY int
select @minY=year(min(dateday)),@maxY=year(max(dateday)) from table_pqs declare @table table(y int identity(0,1),yearD int,beginD char(10),endD char(10)) --100年足够了吧!这里就直接写了100条数据
insert into @table (yearD) select top 100 null from sys.objects a,sys.objects b --生成100年的计算时间表
update @table
set
yearD=@minY+y,
beginD=cast(@minY+y as varchar)+@beginDate,
endD= cast(@minY+y+@kn as varchar)+@EndDate --获取统计数据
select yearD,SUM(T) avgD from @table a inner join table_pqs b
ON dateDay between beginD and endD
group by yearD --如需要时间段
/*
select yearD,beginD,endD,SUM(T) from @table a inner join table_pqs b
ON dateDay between beginD and endD
group by yearD,beginD,endD
*/
END
GO执行存储过程exec dbo.ProGetData '-01-01', '-02-28',1
输出结果
yearD avgD
----------- -----------
2008 6
2009 10
最后代码如下:declare @date1 char(5), @date2 char(5)
set @date1='12-01'
set @date2='02-28'select yyyy,avg(t) as avg_t from
( select yyyy=case when SUBSTRING(CONVERT(VARCHAR(10),DATEDAY,120),6,5) <= @date2
then year(dateday)-1
else year(dateday)
end,dateday ,t
from 表1
where 站号='56386'
and (SUBSTRING(CONVERT(VARCHAR(10),DATEDAY,120),6,5) >= @date1
or SUBSTRING(CONVERT(VARCHAR(10),DATEDAY,120),6,5) <= @date2)
)tb
group by yyyy
order by yyyy