ALTER PROCEDURE [dbo].[UP_GetClassSC_T]
(
@ClassID varchar(10)
)
asdeclare @s nvarchar(4000)
Select @s=isnull(@s+',','')+ quotename([XXCourseName]) from View_ClassSC_NEW group by[XXCourseName]
exec('select [StuName] as 姓名,'+@s+',[平均分] from (select [ClassID],[StuName], [XXCourseName],[FinalScore],[平均分]=avg([FinalScore])over(partition by [StuName])
from View_ClassSC_NEW ) a pivot (max([FinalScore]) for [XXCourseName] in('+@s+' ))b ')
我想给个查询条件来过滤 查询结果 即:--where ClassID=@ClassID
上面的查询只能查出全部的班级的学生成绩,我希望能按照班级ID来查询,请问我该怎么把这个条件( where ClassID=@ClassID
)添加到上面的语句中呢?
(
@ClassID varchar(10)
)
asdeclare @s nvarchar(4000)
Select @s=isnull(@s+',','')+ quotename([XXCourseName]) from View_ClassSC_NEW group by[XXCourseName]
exec('select [StuName] as 姓名,'+@s+',[平均分] from (select [ClassID],[StuName], [XXCourseName],[FinalScore],[平均分]=avg([FinalScore])over(partition by [StuName])
from View_ClassSC_NEW ) a pivot (max([FinalScore]) for [XXCourseName] in('+@s+' ))b ')
我想给个查询条件来过滤 查询结果 即:--where ClassID=@ClassID
上面的查询只能查出全部的班级的学生成绩,我希望能按照班级ID来查询,请问我该怎么把这个条件( where ClassID=@ClassID
)添加到上面的语句中呢?
ALTER PROCEDURE [dbo].[UP_GetClassSC_T]
(
@ClassID varchar(10)
)
asdeclare @s nvarchar(4000)
Select @s=isnull(@s+',','')+ quotename([XXCourseName]) from View_ClassSC_NEW group by[XXCourseName]
exec('select [StuName] as 姓名,'+@s+',[平均分] from (select [ClassID],[StuName], [XXCourseName],[FinalScore],[平均分]=avg([FinalScore])over(partition by [StuName])
from View_ClassSC_NEW where ClassID=@ClassID
) a pivot (max([FinalScore]) for [XXCourseName] in('+@s+' ))b ')
(
@ClassID varchar(10)
)
asdeclare @s nvarchar(4000)
Select @s=isnull(@s+',','')+ quotename([XXCourseName]) from View_ClassSC_NEW group by[XXCourseName]
exec('select [StuName] as 姓名,'+@s+',[平均分] from (select [ClassID],[StuName], [XXCourseName],[FinalScore],[平均分]=avg([FinalScore])over(partition by [StuName])
from View_ClassSC_NEW where ClassID='''+@ClassID+'''
) a pivot (max([FinalScore]) for [XXCourseName] in('+@s+' ))b ')
insert into View_ClassSC_NEW select 'G00101','张三','语文',78
insert into View_ClassSC_NEW select 'G00101','李四','语文',82
insert into View_ClassSC_NEW select 'G00101','张三','数学',88
insert into View_ClassSC_NEW select 'G00101','李四','数学',70
insert into View_ClassSC_NEW select 'G00102','王五','语文',85
insert into View_ClassSC_NEW select 'G00102','曹六','语文',70
insert into View_ClassSC_NEW select 'G00102','刘七','语文',66
go
CREATE PROCEDURE [dbo].[UP_GetClassSC_T]
(
@ClassID varchar(10)
)
as
begin
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+ quotename([XXCourseName]) from View_ClassSC_NEW group by[XXCourseName]
exec('select [StuName] as 姓名,'+@s+',[平均分] from (select [ClassID],[StuName], [XXCourseName],[FinalScore],[平均分]=avg([FinalScore])over(partition by [StuName])
from View_ClassSC_NEW where classid='''+@ClassID+''') a pivot (max([FinalScore]) for [XXCourseName] in('+@s+' ))b ')
end
go
execute UP_GetClassSC_T 'G00101'
go
drop procedure UP_GetClassSC_T
drop table View_ClassSC_NEW
/*
姓名 数学 语文 平均分
-------------------- ----------- ----------- -----------
李四 70 82 76
张三 88 78 83(2 行受影响)
*/