Luouy~羽林
学问如逆水行舟,不进则退; 有知识的人不实践,等于一只蜜蜂不酿蜜; 我们可以由读书而收集知识,但必须利用思考把糠和谷子分开
create table tb  (     
Name    varchar(10) ,     
Subject varchar(10) ,     
Result  int ,
Mark varchar(10)
 )    
insert into tb(Name , Subject , Result,Mark) values('张三' , '语文' , 74,'良好')  
insert into tb(Name , Subject , Result,Mark) values('张三' , '数学' , 83,'好')  
insert into tb(Name , Subject , Result,Mark) values('张三' , '物理' , 93,'很好')  
insert into tb(Name , Subject , Result,Mark) values('李四' , '语文' , 74,'良好')  
insert into tb(Name , Subject , Result,Mark) values('李四' , '数学' , 84,'好')  
insert into tb(Name , Subject , Result,Mark) values('李四' , '物理' , 94,'非常好') 
insert into tb(Name , Subject , Result,Mark) values('李1' , '语文' , 74,'良1好')  
insert into tb(Name , Subject , Result,Mark) values('李1' , '数学' , 84,'好2')  
insert into tb(Name , Subject , Result,Mark) values('李1' , '物理' , 94,'非常3好') 
insert into tb(Name , Subject , Result,Mark) values('李1' , '生物' , 90,'非常好') 

--获取评介(科目和用户) 函数

CREATE FUNCTION getMark(@Group varchar(255),@Name varchar(255))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=Mark FROM tb WHERE Subject=@Group and name = @Name
RETURN(@r)
END
GO   

drop FUNCTION getMark 

---普通写法 

select name 姓名,    
 max(case subject when '语文' then result else 0 end) 语文, dbo.getMark('语文',name) 语文mark,
 max(case subject when '数学' then result else 0 end) 数学, dbo.getMark('数学',name) 数学mark,   
 max(case subject when '物理' then result else 0 end) 物理, dbo.getMark('物理',name) 物理mark

from tb  group by name   

--动态写法 

declare @sql varchar(8000)  
set @sql = 'select Name as ' + '姓名'  
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'  
+',dbo.getMark('''+subject+''',name) '+subject+'mark'
from (select distinct Subject from tb) as a  
set @sql = @sql + ' from tb group by name'  
Print (@sql)
exec(@sql) 

--取平均分

select name 姓名,    
 max(case subject when '语文' then result else 0 end) 语文,    
 max(case subject when '数学' then result else 0 end) 数学,    
 max(case subject when '物理' then result else 0 end) 物理,    
 cast(avg(result*1.0) as decimal(18,2)) 平均分,    

 sum(result) 总分  from tb  group by name   

/*  姓名         语文        数学        物理        平均分                总分 
 李四         74            84            94            84.00                   252     
  张三         74            83            93            83.33                   250  */ 
--动态写法

 declare @sql varchar(8000)  

set @sql = 'select Name as ' + '姓名'  
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'  
from (select distinct Subject from tb) as a 
 set @sql = @sql + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name' 
 exec(@sql) 

 

 

 

 

posted on 2012-08-20 15:45  羽林.Luouy  阅读(353)  评论(0编辑  收藏  举报