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

请教一个分组求平均数的问题!
有这样几条数据
NOTE_NUMBER   NOTE_TIME
78     2007-3-30
87     2007-3-30
88     2007-4-30
87     2007-3-30
87     2007-4-30
74     2007-3-30
72     2007-5-30
76     2007-5-30
37     2007-5-30

想把属于同一个月的NOTE_NUMBER字段值求一个平均值然后同其它月份的数据生成一个记录集
感谢您的解答。。。

------解决方案--------------------
select convert(varchar(7), NOTE_TIME, 126), avg(note_number)
from t_table group by convert(varchar(7), NOTE_TIME, 126)
------解决方案--------------------
declare @t table(NOTE_NUMBER int, NOTE_TIME datetime)
insert @t
select 78, '2007-3-30 ' union all
select 87, '2007-3-30 ' union all
select 88, '2007-4-30 ' union all
select 87, '2007-3-30 ' union all
select 87, '2007-4-30 ' union all
select 74, '2007-3-30 ' union all
select 72, '2007-5-30 ' union all
select 76, '2007-5-30 ' union all
select 37, '2007-5-30 '

SELECT
convert(varchar(7),NOTE_TIME,120) as NOTE_TIME,
avg(NOTE_NUMBER) as NOTE_NUMBER
FROM @t
GROUP BY convert(varchar(7),NOTE_TIME,120)

/*结果
NOTE_TIME NOTE_NUMBER
---------------------
2007-03 81
2007-04 87
2007-05 61
*/

------解决方案--------------------
create table t(NOTE_NUMBER int, NOTE_TIME datetime)
insert t
select 78, '2007-3-30 ' union all
select 87, '2007-3-30 ' union all
select 88, '2007-4-30 ' union all
select 87, '2007-3-30 ' union all
select 87, '2007-4-30 ' union all
select 74, '2007-3-30 ' union all
select 72, '2007-5-30 ' union all
select 76, '2007-5-30 ' union all
select 37, '2007-5-30 '


select datepart(mm,NOTE_TIME) as month ,avg(NOTE_NUMBER)as avg from t
group by datepart(mm,NOTE_TIME)


month avg
----------- -----------
3 81
4 87
5 61

(3 row(s) affected)