求一个SQL语句

Question

SELECT
 MIN(ID)ID,班级,  
AVG(卫生得分)卫生得分,
AVG(  纪律得分) 纪律得分,
AVG(  学习分) 学习分,
DATEPART(MM, 时间)时间 FROM TB 
GROUP BY 班级,
DATEPART(MM, 时间)时间 

Answer 1

DECLARE @T TABLE(ID INT,班级 VARCHAR(10),  卫生得分 INT,  纪律的分 INT, 学习分 INT, 时间 DATETIME) 
INSERT @T SELECT 1  ,  '01',    30  ,      56,        23  ,    '2008-06-02' 
INSERT @T SELECT 2 ,   '01' ,   40 ,       34 ,       90 ,     '2008-06-03' 
INSERT @T SELECT 3,    '03'  ,  80,        34  ,      23,      '2008-06-04' 
SELECT ID=MIN(ID),
       班级 , 
       SUM(卫生得分)*1.0/COUNT(*)卫生得分, 
       SUM( 纪律的分)*1.0/COUNT(*)纪律的分,
       SUM(学习分)*1.0/COUNT(*)学习分,  
       (SUM(卫生得分)+ SUM( 纪律的分)+  SUM(学习分))*1.0/COUNT(*)总分, 
       CONVERT(VARCHAR(7),时间,120) 时间
FROM @T GROUP BY 班级,CONVERT(VARCHAR(7),时间,120)
/*ID          班级         卫生得分                                    纪律的分                                    学习分                                     总分                                      时间
----------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -------
1           01         35.000000000000                         45.000000000000                         56.500000000000                         136.500000000000                        2008-06
3           03         80.000000000000                         34.000000000000                         23.000000000000                         137.000000000000                        2008-06*/

Answer 2

select id =min(id),
            班级, avg(卫生得分),avg(纪律的分),avg(学习分),  
            avg(卫生得分)+avg(纪律的分)+avg(学习分),month(时间)  
from sb19871023 group by 班级, month(时间)

Answer 3

DECLARE @T TABLE(ID INT,班级 VARCHAR(10),  卫生得分 DEC(18,2),  纪律的分 DEC(18,2), 学习分 DEC(18,2), 时间 DATETIME) 
INSERT @T SELECT 1  ,  '01',    30  ,      56,        23  ,    '2008-06-02' 
INSERT @T SELECT 2 ,   '01' ,   40 ,       34 ,       90 ,     '2008-06-03' 
INSERT @T SELECT 3,    '03'  ,  80,        34  ,      23,      '2008-06-04' 
SELECT ID=MIN(ID),
       班级 , 
       AVG(卫生得分)卫生得分, 
       AVG( 纪律的分)纪律的分,
       AVG(学习分)学习分,  
       (AVG(卫生得分)+ AVG( 纪律的分)+  AVG(学习分))总分, 
       MONTH(时间) 时间
FROM @T GROUP BY 班级,MONTH(时间)
/*ID          班级         卫生得分                                    纪律的分                                    学习分                                     总分                                      时间
----------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------
1           01         35.000000                               45.000000                               56.500000                               136.500000                              6
3           03         80.000000                               34.000000                               23.000000                               137.000000                              6*/