原数据结构Id 日期 得分1 得分2 得分3
1 2008-4-1 0 1 2
2 2008-5-1 1 3 0
3 2008-5-26 2 1 2 求结果年 月 得分1平均分 得分2平均分 得分3平均分
2008 4 0 1 2
2008 5 1.5 2 2就是统计分数不是0分的平均分,如2008年5月份得分3平均分是2/1=2,而不是2/2=1
现在只是举例有3个得分,实际情况是有10多项分数。谢谢
1 2008-4-1 0 1 2
2 2008-5-1 1 3 0
3 2008-5-26 2 1 2 求结果年 月 得分1平均分 得分2平均分 得分3平均分
2008 4 0 1 2
2008 5 1.5 2 2就是统计分数不是0分的平均分,如2008年5月份得分3平均分是2/1=2,而不是2/2=1
现在只是举例有3个得分,实际情况是有10多项分数。谢谢
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (Id int,日期 datetime,得分1 int,得分2 int,得分3 int)
insert into #T
select 1,'2008-4-1',0,1,2 union all
select 2,'2008-5-1',1,3,0 union all
select 3,'2008-5-26',2,1,2
go
select year(日期) 年,month(日期) 月,
avg(得分1) 得分1平均,
avg(得分2) 得分2平均,
avg(得分3) 得分3平均
from #T
group by year(日期) ,month(日期)
go
drop table #T/*
年 月 得分1平均 得分2平均 得分3平均
----------- ----------- ----------- ----------- -----------
2008 4 0 1 2
2008 5 1 2 1(2 row(s) affected)
*/
from ta
group bydatepart(yy,日期),datepart(mm,日期)
create table #T (Id int,日期 datetime,得分1 int,得分2 int,得分3 int)
insert into #T
select 1,'2008-4-1',0,1,2 union all
select 2,'2008-5-1',1,3,0 union all
select 3,'2008-5-26',2,1,2
goselect 年 =datepart(yy,日期),月 =datepart(mm,日期),avg(得分1*1.0) as 得分1,avg(得分2*1.0) as 得分2,avg(得分3*1.0) as 得分3
from #t
group by datepart(yy,日期),datepart(mm,日期)/*年 月 得分1 得分2 得分3
----------- ----------- ---------------------------------------- ---------------------------------------- ----------------------------------------
2008 4 .000000 1.000000 2.000000
2008 5 1.500000 2.000000 1.000000(所影响的行数为 2 行)
*/
得分2=case when 0 then 0 else then avg(得分1) end,
得分3=case when 0 then 0 else then avg(得分1) end
group by datepart(yy,日期),datepart(m,日期)
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (Id int,日期 datetime,得分1 int,得分2 int,得分3 int)
insert into #T
select 1,'2008-4-1',0,1,2 union all
select 2,'2008-5-1',1,3,0 union all
select 3,'2008-5-26',2,1,2select
年=year(日期),
月=month(日期),
得分1平均分=1.0*sum(得分1)/nullif(sum(case when 得分1>0 then 1 else 0 end),0),
得分2平均分=1.0*sum(得分2)/nullif(sum(case when 得分2>0 then 1 else 0 end),0),
得分3平均分=1.0*sum(得分3)/nullif(sum(case when 得分3>0 then 1 else 0 end),0)
from #T group by year(日期),month(日期)/*
年 月 得分1平均分 得分2平均分 得分3平均分
----------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------
2008 4 NULL 1.000000000000 2.000000000000
2008 5 1.500000000000 2.000000000000 2.000000000000
*/
得分2=case 得分2 when 0 then 0 else avg(得分2) end,
得分3=case 得分3 when 0 then 0 else avg(得分3) end
group by datepart(yy,日期),datepart(m,日期)
insert @a select 1,'2008-4-1',0,1,2
union ALL select 2,'2008-5-1',1,3,0
union ALL select 3,'2008-5-26',2,1,2
SELECT year(日期) 年,month(日期) 月,
得分1平均=case when sum(得分1)=0 then 0 else sum(得分1)*1.0/sum(case when 得分1=0 then 0 else 1 end ) end,
得分2平均=case when sum(得分2)=0 then 0 else sum(得分2)*1.0/sum(case when 得分2=0 then 0 else 1 end ) end ,
得分3平均=case when sum(得分3)=0 then 0 else sum(得分3)*1.0/sum(case when 得分3=0 then 0 else 1 end) end
FROM @a
GROUP BY year(日期),month(日期)--result
/*年 月 得分1平均 得分2平均 得分3平均
----------- ----------- -------------------------- -------------------------- --------------------------
2008 4 .000000000000 1.000000000000 2.000000000000
2008 5 1.500000000000 2.000000000000 2.000000000000(所影响的行数为 2 行)
*/