200分急求成绩统计SQL语句....不够再开贴加分....谢谢......

[yrs007]

考试信息表：a
ID,examName(考试名称),classID(班别ID号)
成绩表：b
ID,examID,fraction(分数),studentID(学号)
班级表:c
ID className(班名)
学生表:d
studentID,studentName,classID(班别ID号)

每添加一条考试信息，生成整个班学生的成绩记录，如添加某班的数字，则成绩表会生成某班所有学生的数字成绩记录，每学生一条记录。
表如下数据：
班级表：c
ID className
1 一班
2 二班

学生表:d (classID对方班级的ID号)
studentID studentName classID
2001 小明 1
2002 小刘 1
2003 小红 1
2004 小张 1
3003 小李 2
3004 小杨 2

考试信息表:a (classID对方班级的ID号)
ID examName classID
1 数学 1
2 语文 1
3 英语 1
4 体育 1

成绩表：b (examID对应考试信息ID号)
ID examID fraction studentID
1 1 80 2001
2 1 90 2002
3 1 80 2003
4 1 70 2004
5 3 90 2001
6 3 90 2002
7 3 86 2003
8 3 70 2004
9 2 60 2001
10 2 60 2002
11 2 80 2003
12 2 70 2004

需要查询结果如下显示
studentID studentName 班别 数字 英语 语文 总分 平均分
2001 小明 一班 80 90 60 230 76.67
2002 小刘 一班 90 90 60 240 80
2003 小红 一班 80 86 80 246 82
2004 小张 一班 70 70 70 210 70

不知道我表达清楚没有，求统计语句，非常感谢。。。。。

[misterliwei]

在考试信息表上增加一个触发器：

CREATE TRIGGER TRI_考试信息表_INSERT 
ON 考试信息表
AFTER INSERT
AS BEGIN
   INSERT INTO 成绩表(examID,fraction,studentID)
   SELECT A.ID, NULL, STUDENTID
   FROM INSERTED A
   JOIN 学生表 D
  ON A.CLASSID = D.CLASSID
END

[yrs007]

各个大神，还有什么比较简单的方法没有。。。。谢谢。

[-小蕾-]

select studentID,studentName,className,
max(case examName when '语文' then fraction else 0 end)语文,
max(case examName when '数学' then fraction else 0 end)数学,
max(case examName when '英语' then fraction else 0 end)英语,
--max(case examName when '体育' then fraction else 0 end)体育,
sum(fraction)总分,
cast(avg(fraction*1.0) as decimal(18,2))平均分
from 
(
select d.studentID,d.studentName,c.className,a.examName,fraction from b
,a,c,d where b.examID=a.ID and c.ID=a.classID and b.studentID=d.studentID
) as table1
group by studentID,studentName,className order by studentID

[xxfvba]

declare @s varchar(max) select @s=ISNULL(@s+',','')+'max(case when examName='''+examname+''' then fraction else 0 end) as ['+examName+']' from b,a where a.id=b.examid group by examname set @s='select b.studentID,d.studentName,c.className,'+@s+',sum(fraction),avg(fraction) from b,a,c,d where a.id=b.examid and d.studentID=b.studentID and a.classID=c.classID group by b.studentID,d.studentName,c.className' print @s exec(@s)

[Tiger_Zhao]

/* 测试数据
;WITH t(ID,examName,classID)AS(
    SELECT 1,'数学',1 UNION ALL
    SELECT 2,'语文',1 UNION ALL
    SELECT 3,'英语',1 UNION ALL
    SELECT 4,'体育',1
)
SELECT * INTO a FROM t

;WITH t(ID,examID,fraction,studentID)AS(
    SELECT 1,1,80,2001 UNION ALL
    SELECT 2,1,90,2002 UNION ALL
    SELECT 3,1,80,2003 UNION ALL
    SELECT 4,1,70,2004 UNION ALL
    SELECT 5,3,90,2001 UNION ALL
    SELECT 6,3,90,2002 UNION ALL
    SELECT 7,3,86,2003 UNION ALL
    SELECT 8,3,70,2004 UNION ALL
    SELECT 9,2,60,2001 UNION ALL
    SELECT 10,2,60,2002 UNION ALL
    SELECT 11,2,80,2003 UNION ALL
    SELECT 12,2,70,2004
)
SELECT * INTO b FROM t

;WITH t(ID,className)AS(
    SELECT 1,'一班' UNION ALL
    SELECT 2,'二班'
)
SELECT * INTO c FROM t

;WITH t(studentID,studentName,classID)AS(
    SELECT 2001,'小明',1 UNION ALL
    SELECT 2002,'小刘',1 UNION ALL
    SELECT 2003,'小红',1 UNION ALL
    SELECT 2004,'小张',1 UNION ALL
    SELECT 3003,'小李',2 UNION ALL
    SELECT 3004,'小杨',2
)
SELECT * INTO d FROM t
*/

-- 参数：班级
DECLARE @classID int
SET @classID = 1

DECLARE @sql nvarchar(max)
DECLARE @columns nvarchar(200)
DECLARE @columnNames nvarchar(200)

SET @columns = N''
SET @columnNames = N''

    SELECT @columns = @columns+N'['+CONVERT(nvarchar(11),ID)+N'],',
           @columnNames = @columnNames+N'['+CONVERT(nvarchar(11),ID)+N'] AS ['+examName+N'],'
      FROM a
     WHERE classID = @classID
  ORDER BY ID

SET @columns = @columns+N'[10001],[10002]'

SET @columnNames = @columnNames+N'[10001] AS [总分],[10002] AS [平均分]'

SET @sql = N'
WITH x AS (
    SELECT d.studentID,
           d.studentName,
           c.className AS 班别,
           b.examID,
           CONVERT(decimal(5,2),b.fraction) fraction
      FROM c
      JOIN d
        ON c.ID = d.classID
      JOIN b
        ON d.studentID = b.studentID
     WHERE c.ID = @classID
)
,y AS(
        SELECT *
          FROM x
    UNION ALL
        SELECT studentID,
               studentName,
               班别,
               10001 examID,
               CONVERT(decimal(5,2),SUM(fraction)) fraction
          FROM x
      GROUP BY studentID, studentName, 班别
    UNION ALL
        SELECT studentID,
               studentName,
               班别,
               10002 examID,
               CONVERT(decimal(5,2),AVG(fraction*1.0)) fraction
          FROM x
      GROUP BY studentID, studentName, 班别
)
SELECT studentID,
       studentName,
       班别,
       '+@columnNames+N'
  FROM y
 PIVOT (
        MAX(fraction)
        FOR examID IN ('+@columns+N')
       ) P
 ORDER BY studentID'

PRINT @sql

EXEC sp_executesql @sql,
                   N'@classID int',
                   @classID = @classID

动态语句

WITH x AS (
    SELECT d.studentID,
           d.studentName,
           c.className AS 班别,
           b.examID,
           CONVERT(decimal(5,2),b.fraction) fraction
      FROM c
      JOIN d
        ON c.ID = d.classID
      JOIN b
        ON d.studentID = b.studentID
     WHERE c.ID = @classID
)
,y AS(
        SELECT *
          FROM x
    UNION ALL
        SELECT studentID,
               studentName,
               班别,
               10001 examID,
               CONVERT(decimal(5,2),SUM(fraction)) fraction
          FROM x
      GROUP BY studentID, studentName, 班别
    UNION ALL
        SELECT studentID,
               studentName,
               班别,
               10002 examID,
               CONVERT(decimal(5,2),AVG(fraction*1.0)) fraction
          FROM x
      GROUP BY studentID, studentName, 班别
)
SELECT studentID,
       studentName,
       班别,
       [1] AS [数学],[2] AS [语文],[3] AS [英语],[4] AS [体育],[10001] AS [总分],[10002] AS [平均分]
  FROM y
 PIVOT (
        MAX(fraction)
        FOR examID IN ([1],[2],[3],[4],[10001],[10002])
       ) P
 ORDER BY studentID

结果

  studentID studentName 班别     数学     语文     英语     体育     总分   平均分
----------- ----------- ---- -------- -------- -------- -------- -------- --------
       2001 小明        一班    80.00    60.00    90.00     NULL   230.00    76.67
       2002 小刘        一班    90.00    60.00    90.00     NULL   240.00    80.00
       2003 小红        一班    80.00    80.00    86.00     NULL   246.00    82.00
       2004 小张        一班    70.00    70.00    70.00     NULL   210.00    70.00

[Tiger_Zhao]

要么动态语句部分你用前台程序拼接，就是一句语句而已。

[yrs007]

引用 6 楼 Tiger_Zhao 的回复:

要么动态语句部分你用前台程序拼接，就是一句语句而已。

麻烦提供一下，谢谢。。。

[Tiger_Zhao]

[Quote=引用 4 楼 Tiger_Zhao 的回复:]动态语句

WITH x AS (
...

[/Quote]
已提供了啊。