求用SQL语句求多个学期的学生科目平均分语句

Question

我的数据库结构大概是这样
学号    姓名  班别   科目   总评成绩  补考成绩   学期
a001    小明   A班   语文    80         null      2006-2007第一学期
a002    小红   A班   语文    40         60        2006-2007第一学期
a003    小白   A班   语文    50         null      2006-2007第一学期
a001    小明   A班   语文    78         null      2006-2007第二学期
。即某一学科可能会学多个学期，现在我想计毕业成绩，成绩为每科多个学期的平均成绩。
但如果该生总评不合格的，就要看补考成绩是否高于总评，高过的就要算补考成绩（但存在总评不合格，补考是NULL的情况），而补考成绩合格的只计60分。。大概就是这样，用编程循环来计算是可以，但太慢了，所以想一条SQL语句搞定谢谢了~~

Answer 1

先用case将合格的补考成绩替换到总成绩
select 学号,姓名,班别,科目,case when 总评成绩<isnull(补考成绩,0) then 补考成绩 else 总评成绩 end as 总评成绩,学期 from tb

Answer 2

SELECT 学号  ,  姓名,  班别,  科目 , 总评成绩 , ISNULL(补考成绩,0)补考成绩 , 学期  FROM TB SELECT 学号  ,  姓名,  班别,  科目,AVG(总评成绩)平均成绩  FROM 
(SELECT 学号  ,  姓名,  班别,  科目 ,CASE WHEN 总评成绩 >补考成绩 THEN 总评成绩 ELSE 补考成绩  END AS 总评成绩  , 学期 FROM TB )AS T
GROUP BY 学号  ,  姓名,  班别,  科目

Answer 3

--try
select 学号,姓名,科目,sum(总评成绩)/sum(1) as 平均成绩 from (select 学号,姓名,班别,科目,case when 总评成绩<isnull(补考成绩,0) then 补考成绩 else 总评成绩 end as 总评成绩,学期 from tb) t group by 学号,姓名,科目 order by 学号,姓名,科目

Answer 4

汗,我居然忘了avg()函数,面壁去也.

Answer 5

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-06-17 09:43:14
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([学号] varchar(4),[姓名] varchar(4),[班别] varchar(3),[科目] varchar(4),[总评成绩] int,[补考成绩] int,[学期] varchar(17))
insert [tb]
select 'a001','小明','A班','语文',80,null,'2006-2007第一学期' union all
select 'a002','小红','A班','语文',40,60,'2006-2007第一学期' union all
select 'a003','小白','A班','语文',50,null,'2006-2007第一学期' union all
select 'a001','小明','A班','语文',78,null,'2006-2007第二学期'
--------------开始查询--------------------------select 学号,姓名,班别,科目,case when 总评成绩<isnull(补考成绩,0) then 补考成绩 else 总评成绩 end as 总评成绩,学期 from tb----------------结果----------------------------
/*学号  姓名    班别     科目   总评成绩       学期
a001 小明 A班 语文 80 2006-2007第一学期
a002 小红 A班 语文 60 2006-2007第一学期
a003 小白 A班 语文 50 2006-2007第一学期
a001 小明 A班 语文 78 2006-2007第二学期*/

Answer 6

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-06-17 09:43:14
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([学号] varchar(4),[姓名] varchar(4),[班别] varchar(3),[科目] varchar(4),[总评成绩] int,[补考成绩] int,[学期] varchar(17))
insert [tb]
select 'a001','小明','A班','语文',80,null,'2006-2007第一学期' union all
select 'a002','小红','A班','语文',40,60,'2006-2007第一学期' union all
select 'a003','小白','A班','语文',50,null,'2006-2007第一学期' union all
select 'a001','小明','A班','语文',78,null,'2006-2007第二学期'
--------------开始查询--------------------------select 学号,姓名,科目,sum(总评成绩)/sum(1) as 平均成绩 
from 
(select 学号,姓名,班别,科目,case when 总评成绩<isnull(补考成绩,0) then 补考成绩 else 总评成绩 end as 总评成绩,学期 from tb) t 
group by 学号,姓名,科目 order by 学号,姓名,科目----------------结果----------------------------
/*学号  姓名    班别     总评成绩
a001 小明 语文 79
a002 小红 语文 60
a003 小白 语文 50
*/

Answer 7

select 
学号,
姓名,
班别,
学期, 
科目,
总评成绩=case when 总评成绩<isnull(补考成绩,0) then 补考成绩 else 总评成绩 end
from tb

Answer 8

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([学号] varchar(4),[姓名] varchar(4),[班别] varchar(3),[科目] varchar(4),[总评成绩] int,[补考成绩] int,[学期] varchar(17))
insert [tb]
select 'a001','小明','A班','语文',80,null,'2006-2007第一学期' union all
select 'a002','小红','A班','语文',40,60,'2006-2007第一学期' union all
select 'a003','小白','A班','语文',50,null,'2006-2007第一学期' union all
select 'a001','小明','A班','语文',78,null,'2006-2007第二学期'
select 学号,姓名,科目,sum(总评成绩)/sum(1) as 平均成绩 
from (select 学号,姓名,班别,科目,
 case when 总评成绩<isnull(补考成绩,0) then 补考成绩 else 总评成绩 end as 总评成绩,学期 from tb) t group by 学号,姓名,科目 order by 学号,姓名,科目/*学号   姓名    科目   平均成绩
a001 小明 语文 79
a002 小红 语文 60
a003 小白 语文 50*/

Answer 9

 ;WITH LI AS 
(
SELECT avg(总评成绩) as 总评成绩, MAX(isnull(补考成绩,0)) as 补考成绩, 
       学号,姓名,科目 
FROM LI 
GROUP BY 学号,姓名,科目) SELECT 学号,姓名,科目, 
       (CASE WHEN 补考成绩  >= 总评成绩 then 60   
               else 总评成绩 end)  as 成绩 
FROM LI 
WHERE 总评成绩 < 60
union all
select   学号,姓名,科目,总评成绩
FROM LI 
WHERE 总评成绩 >= 60

Answer 10

还是倾向于用avg的
select 学号,姓名,科目,avg(总评成绩) as 平均成绩
from 
(select 学号,姓名,班别,科目,case when 总评成绩<isnull(补考成绩,0) then 补考成绩 else 总评成绩 end as 总评成绩,学期 from tb) t 
group by 学号,姓名,科目 
order by 学号,姓名,科目

Answer 11

好像也有点错误!!!
SELECT 学号  ,  姓名,  班别,  科目,AVG(总评成绩)平均成绩  FROM 
(SELECT 学号  ,  姓名,  班别,  科目 ,
CASE WHEN 总评成绩 >补考成绩 THEN 总评成绩 ELSE 补考成绩  END AS 总评成绩  , 学期 FROM 
(SELECT 学号  ,  姓名,  班别,  科目 , 总评成绩 , ISNULL(补考成绩,0)补考成绩 , 学期  FROM TB )AS T1)AS T
GROUP BY 学号  ,  姓名,  班别,  科目

Answer 12

if object_id('[LI]') is not null drop table [LI]
GO 
create table [LI]([学号] varchar(4),[姓名] varchar(4),[班别] varchar(3),[科目] varchar(4),
                  [总评成绩] int,[补考成绩] int,[学期] varchar(17))
insert INTO [LI] ([学号],[姓名],[班别],[科目],[总评成绩],[补考成绩],[学期])
select 'a001','小明','A班','语文',80,null,'2006-2007第一学期' union all
select 'a002','小红','A班','语文',40,60,'2006-2007第一学期' union all
select 'a003','小白','A班','语文',50,null,'2006-2007第一学期' union all
select 'a001','小明','A班','语文',78,null,'2006-2007第二学期'
 ;WITH TLI AS 
(
SELECT avg(总评成绩) as 总评成绩, MAX(isnull(补考成绩,0)) as 补考成绩, 
       学号,姓名,科目 
FROM LI 
GROUP BY 学号,姓名,科目) SELECT 学号,姓名,科目, 
       (CASE WHEN 补考成绩  >= 总评成绩 then 60   
               else 总评成绩 end)  as 成绩 
FROM TLI 
WHERE 总评成绩 < 60
union all
select   学号,姓名,科目,总评成绩
FROM TLI 
WHERE 总评成绩 >= 60
/*
学号   姓名   科目   成绩
---- ---- ---- -----------
a002 小红   语文   60
a003 小白   语文   50
a001 小明   语文   79(3 行受影响)
*/

Answer 13

select 学号,姓名,科目,平均成绩=sum(总评成绩)/count(1) 
from 
(
select 学号,姓名,班别,科目,学期,总评成绩=(case when 总评成绩<isnull(补考成绩,0) then 补考成绩 else 总评成绩) from @tb
) A 
group by 学号,姓名,科目 
order by 学号,姓名,科目
/*
学号   姓名    科目   平均成绩
a001    小明    语文    79
a002    小红    语文    60
a003    小白    语文    50(3 行受影响)
*/