日期:2014-05-18  浏览次数:20645 次

小计均值问题
SQL code

CREATE TABLE #tb(FData datetime,FNum int)
INSERT INTO #tb 
select '2011-1-1',100 UNION ALL
--         均值    100    
select '2012-1-2',100 UNION ALL
select '2012-1-25',200 UNION ALL
--         均值    150     
select '2012-2-3',100 UNION ALL
select '2012-2-25',200 UNION ALL
--         均值    150
select '2012-2-26',100 UNION ALL
select '2012-3-7',100 UNION ALL
--         均值    100
select '2012-4-9',200 UNION ALL
--         均值    200
select '2012-11-10',200 UNION ALL
--         均值    200
select '2012-11-26',100 UNION ALL
select '2012-12-31',200 
--         均值    150

-- 月份均值查询范围:上月26-本月25,12月份:11月26-12月31,1月份:1月1日-1月25日
 
select * from #tb
union 
select Max(FData),avg(FNum) from #tb

--怎么查出结果是类似上面
记录
均值
记录
均值

的显示出来





------解决方案--------------------
SQL code
;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,FNum
from
(
select convert(varchar(7),td,120) as px,convert(varchar(10),fdata,120) as fdata,FNum from bnsr
union all 
select convert(varchar(7),td,120) as px,convert(varchar(7),td,120) as tm,avg(fnum) as fnum from bnsr
group by convert(varchar(7),td,120)
) t2
order by px,len(fdata) desc,fdata

/**
FData      FNum
---------- -----------
2011-01-01 100
           100
2012-01-02 100
2012-01-25 200
           150
2012-02-03 100
2012-02-25 200
           150
2012-02-26 100
2012-03-07 100
           100
2012-04-09 200
           200
2012-11-10 200
           200
2012-11-26 100
2012-12-31 200
           150

(18 行受影响)
**/