Question

SQL求总分及平均值

用Access或mysql或mssql生成一个表test
字段有：id int（自动编号）,学号 int,科目 char(20),成绩 int
数据有：
1)-,1,语文,60
2)-,1,数学,70
3)-,1,英语,75
4)-,2,语文,55
5)-,2,数学,53
6)-,2,英语,71
7)-,3,语文,80
8)-,4,数学,0
9)-,5,英语,91
……
编程要求，把所有人的各学科成绩及总分、平均分列出来，使用最简短的SQL语句来实现，每人一条记录，使用ADO数据库控件和网格来显示，如下：
注意：科目的个数是动态的（不确定的），要从上表中读出共多少科。
学号 语文 数学 英语 ... 总分 平均分
1 60 70 75 ... 205 68.3
2 55 53 71 ... 179 59.67
3 80 0或null 0或null ... 80 26.67
.....
.....
或者
学号 语文 数学 物理 化学 英语 政治 ... 总分 平均分
.....
.....
(可用动态语句创建临时表，或也可直接用组合SQL语句)

我的邮箱地址是:qinjian-86@163.com
测试通过才能给分.
编程要求：用delphi以ADO方式访问数据库。能够分别用网格来显示执行SQL语句前后的数据。最好能给出完整的代码---关键是TADOQuery的ADOQuery.SQL.Add('')；

Answer 1

SQL求总分及平均值的方法。

如下参考：

1.打开数据库软件，添加数据库，右键点击并选择新查询。

2.输出带SQL语句的类zd名称，附加项的平均分，命令是selectclasstableversion。类名，avg(分数)为平均分。

3.将学生表和班级表与班级号连接起来，然后将结果表和学生表与学生号连接起来。

4.数据表关联一般是使用联接命令，该命令是来自学生表联接类表上的类表。

5.将类名分组。该命令是groupbyclass表。

Answer 2

--SQL SERVER 2000 静态SQL
select 学号 as 学号 ,
max(case 科目 when '语文' then 成绩 else 0 end) 语文,
max(case 科目 when '数学' then 成绩 else 0 end) 数学,
max(case 科目 when '英语' then 成绩 else 0 end) 英语,
sum(成绩) 总分,
cast(avg(成绩*1.0) as decimal(18,2)) 平均分
from test
group by 学号

--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 学号 '
select @sql = @sql + ' , max(case 科目 when ''' + 科目 + ''' then 成绩 else 0 end) [' + 科目 + ']'
from (select distinct 科目 from test) as a
set @sql = @sql + ' , cast(avg(成绩*1.0) as decimal(18,2)) 平均分 , sum(成绩) 总分 from test group by 学号'
exec(@sql)

对补充问题的回答：
没使用过Delphi，但下面的问题就是怎么使用控件的问题，这不难了，如果有问题，你可以在百度搜索你要使用的控件名称，会有很多办法的。没时间帮你研究了，你试试吧，况且你尝试搞定的过程会让你收益匪浅的。

Answer 3

create table test(id int identity, 学号 int, 科目 char(20), 成绩 int)

insert test(学号, 科目, 成绩)
select 1,'语文',60 union all
select 1,'数学',70 union all
select 1,'英语',75 union all
select 2,'语文',55 union all
select 2,'数学',53 union all
select 2,'英语',71 union all
select 3,'语文',80 union all
select 4,'数学',0 union all
select 5,'英语',91

declare @s varchar(8000)
select @s='select a.学号 '
select @s=@s+',max(case 科目 when ''' + 科目 + ''' then 成绩 end) ' + 科目
from test
group by 科目
order by 科目
select @s=@s+' , b.总分, b.平均分 from test a
join (select 学号, sum(成绩) 总分, avg(1.0*成绩) 平均分 from test group by 学号) b
on a.学号 = b.学号 group by a.学号, b.总分, b.平均分'
exec(@s)

drop table test
/*
学号 数学 英语 语文 总分 平均分
----------- ----------- ----------- ----------- ----------- ---------------------------------------
1 70 75 60 205 68.333333
2 53 71 55 179 59.666666
3 NULL NULL 80 80 80.000000
4 0 NULL NULL 0 0.000000
5 NULL 91 NULL 91 91.000000
*/

Answer 4

select 学号,
case 科目 when '语文' then 成绩 end as 语文,
case 科目 when '数学' then 成绩 end as 数学,
case 科目 when '英语' then 成绩 end as 英语,
sum(成绩) as 总分，avg(成绩) as 平均分 from test group by 学号,科目