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)
