34,588
社区成员
发帖
与我相关
我的任务
分享
alter proc usp_GetCrossScore4(
@StuName varchar(10)
)
as
create table @ScoreTbl(
ScoreId int,
SubjectName varchar(50),
StudentName varchar(10),
ScoreValue real,
ExamDate datetime,
AvgFlag bit DEFAULT(0)
)
insert into #ScoreTbl
(ScoreId,SubjectName,StudentName,ScoreValue,ExamDate)
select ScoreId,SubjectName,StudentName,ScoreValue,ExamDate from t_Score s where s.StudentName=@StuName
insert into #ScoreTbl(ScoreId,SubjectName,StudentName,ScoreValue,ExamDate,AvgFlag)
select null,SubjectName,StudentName,avg(ScoerValue),null,1 from #ScoreTbl s group by SubjectName,StudentName
declare @sql varchar(8000)
set @sql='select SubjectName 科目'
select @sql=@sql+'SUM(CASE ExamDate when'''+convert(varchar(20),ExamDate,102)+'''then SocreValue Else null end )'''+cast(year(ExamDate)as varchar)+'年'+cast(month(ExamDate)as varchar)+'月'
+cast(day(ExamDate)as varchar)+'日'''
from (select distinct ExamDate from t_Score s where s.StudentName=@StuName)ss
set @sql=@sql+',sum(case AvgFlag when 1 then ScoreValue else null end)''平均分'''
exec(@sql+'from @ScoreTbl s group by Subjectname')