考试信息表：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
不知道我表达清楚没有，求统计语句，非常感谢。。。。。
------解决思路----------------------
在考试信息表上增加一个触发器：
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
------解决思路----------------------
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
------解决思路----------------------
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)
------解决思路----------------------
/* 测试数据
;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
------解决思路----------------------
要么动态语句部分你用前台程序拼接，就是一句语句而已。
------解决思路----------------------
已提供了啊。