小计均值问题
CREATE TABLE #tb(FData datetime,FNum int)INSERT INTO #tb select '2011-1-1',100 UNION ALL-- 均值 100 select '2012-1-2',100 UNION ALLselect '2012-1-25',200 UNION ALL-- 均值 150 select '2012-2-3',100 UNION ALLselect '2012-2-25',200 UNION ALL-- 均值 150select '2012-2-26',100 UNION ALLselect '2012-3-7',100 UNION ALL-- 均值 100select '2012-4-9',200 UNION ALL-- 均值 200select '2012-11-10',200 UNION ALL-- 均值 200select '2012-11-26',100 UNION ALLselect '2012-12-31',200 -- 均值 150-- 月份均值查询范围:上月26－本月25，12月份:11月26－12月31，1月份：1月1日－1月25日 select * from #tbunion select Max(FData),avg(FNum) from #tb--怎么查出结果是类似上面记录均值记录均值的显示出来
;with bnsr as( select *,case when month(fdata)!=12 and datepart(dd,fdata)>25 then dateadd(mm,1,fdata) else fdata end as td from #tb)select case when len(fdata)=7 then '' else fdata end as FData,FNumfrom(select convert(varchar(7),td,120) as px,convert(varchar(10),fdata,120) as fdata,FNum from bnsrunion all select convert(varchar(7),td,120) as px,convert(varchar(7),td,120) as tm,avg(fnum) as fnum from bnsrgroup by convert(varchar(7),td,120)) t2order by px,len(fdata) desc,fdata/**FData FNum---------- -----------2011-01-01 100 1002012-01-02 1002012-01-25 200 1502012-02-03 1002012-02-25 200 1502012-02-26 1002012-03-07 100 1002012-04-09 200 2002012-11-10 200 2002012-11-26 1002012-12-31 200 150(18 行受影响)**/
