declare @s varchar(8000)
set @s=''
select @s=@s+',['+cast(課程編號 as varchar)+']=sum(case 課程編號 when '+cast(課程編號 as varchar)+' then 課程編號+分数 else null end)'
from StudentMark group by 課程編號
exec('select 学号'+@s+' from StudentMark group by 学号')
set @s=''
select @s=@s+',['+cast(課程編號 as varchar)+']=sum(case 課程編號 when '+cast(課程編號 as varchar)+' then 課程編號+分数 else null end)'
from StudentMark group by 課程編號
exec('select 学号'+@s+' from StudentMark group by 学号')
declare @s varchar(8000),@i int
select top 1 @s='',@i=count(*)
from StudentMark
group by StdID,Type
order by count(*) desc
while @i>0
select @s=',編號'+cast(@i as varchar)
+'=max(case bh when '+cast(@i as varchar)
+' then CourseID+cast(Mark as varchar) end)'
+@s
,@i=@i-1
exec('select 學號=StdID'+@s+',平均分=avg(Mark),考試編號=Type
from(
select StdID,CourseID,Mark,Type,bh=(
select count(*) from StudentMark
where StdID=a.StdID and Type=a.Type and id<=a.id)
from StudentMark a
)a group by StdID,Type')
select top 1 @s='',@i=count(*)
from StudentMark
group by StdID,Type
order by count(*) desc
while @i>0
select @s=',編號'+cast(@i as varchar)
+'=max(case bh when '+cast(@i as varchar)
+' then CourseID+cast(Mark as varchar) else '''' end)'
+@s
,@i=@i-1
exec('select 學號=StdID'+@s+',平均分=cast(avg(Mark) as decimal(10,2)),考試編號=Type
from(
select StdID,CourseID,Mark,Type,bh=(
select count(*) from StudentMark
where StdID=a.StdID and Type=a.Type and id<=a.id)
from StudentMark a
)a group by StdID,Type')
create table StudentMark(ID int,StdID char(6),CourseID char(5),Mark float,Type int)
insert StudentMark select 1, '00533','語文',50, 1
union all select 2, '00533','數學',70, 1
union all select 3, '00533','英語',60, 1
union all select 4, '00534','語文',70, 1
union all select 5, '00534','數學',53, 1
union all select 6, '00534','英語',58, 1
union all select 7, '00534','語文',90, 2
union all select 8, '00534','數學',83, 2
union all select 9, '00534','英語',88, 2
union all select 10,'00534','法學',100,2
go--查询
declare @s varchar(8000),@i int
select top 1 @s='',@i=count(*)
from StudentMark
group by StdID,Type
order by count(*) desc
while @i>0
select @s=',編號'+cast(@i as varchar)
+'=max(case bh when '+cast(@i as varchar)
+' then CourseID+cast(Mark as varchar) else '''' end)'
+@s
,@i=@i-1
exec('select 學號=StdID'+@s+',平均分=cast(avg(Mark) as decimal(10,2)),考試編號=Type
from(
select StdID,CourseID,Mark,Type,bh=(
select count(*) from StudentMark
where StdID=a.StdID and Type=a.Type and id<=a.id)
from StudentMark a
)a group by StdID,Type')
go--删除测试
drop table StudentMark/*--测试结果學號 編號1 編號2 編號3 編號4 平均分 考試編號
------ ------------ ----------- ------------ ----------- ----------- ------------
00533 語文 50 數學 70 英語 60 60.00 1
00534 語文 70 數學 53 英語 58 60.33 1
00534 語文 90 數學 83 英語 88 法學 100 90.25 2
--*/