请教各位一个查询问题,数据库是sql2000
有这样的表
begin_date end_date sum_value
2011-01-01 2011-01-10 100
要查出这样的记录
date_value avg_value
2011-01-01 10
2011-01-02 10
2011-01-03 10
2011-01-04 10
2011-01-05 10
2011-01-06 10
2011-01-07 10
2011-01-08 10
2011-01-09 10
2011-01-10 10
请问应该怎么写sql?
就是把sum_value算出时间段天数的平均值.
其实是这样的
begin_date end_date sum_value
2011-01-01 2011-01-05 100
2011-01-08 2011-01-15 200
像这样的数据,我要查出2011-01-03到2011-01-10
这个时间段的总数(两个时间段的总数不等,而且中间时间段可能有间隔)
如果不生成每天的平均值,有什么办法算出来吗?
先谢谢各位了.
补充一下,可以不用游标最好,因为我现在就是用游标遍历表的每个时间段记录,再根据每条记录间隔天数生成一个临时表,
但是由于数据量比较大,用游标太慢.
------解决方案--------------------
- SQL code
create table gxsww(begin_date datetime, end_date datetime, sum_value int)insert into gxswwselect '2011-01-01', '2011-01-05', 100 union allselect '2011-01-08', '2011-01-15', 200selectdateadd(d,b.number,a.begin_date) 'date_value',a.average 'avg_value'from (select begin_date,datediff(d,begin_date,end_date) d,sum_value/(datediff(d,begin_date,end_date)+1) averagefrom gxsww) ainner join master.dbo.spt_values bon b.[type]='P' and b.number between 0 and a.ddate_value avg_value------------ -----------2011-01-01 202011-01-02 202011-01-03 202011-01-04 202011-01-05 202011-01-08 252011-01-09 252011-01-10 252011-01-11 252011-01-12 252011-01-13 252011-01-14 252011-01-15 25(13 row(s) affected)