f not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int,[Time] varchar(20))
Insert Class
select N'张三',N'语文',78,'期末考试' union all
select N'张三',N'数学',87,'期末考试' union all
select N'张三',N'英语',82,'期末考试' union all
select N'张三',N'物理',90,'期末考试' union all
select N'李四',N'语文',65,'期末考试' union all
select N'李四',N'英语',65,'期末考试' union all
select N'李四',N'物理',85,'期末考试'
Go---select * from Class--用2000的方法(动态和静态两种方法得到如下结果该如何来写):要求: 没有考试的成绩记0分,课程是固定的
Student Course1 Score1 Course2 Score2 Course3 Score 3 Course4 Score4 Time 总成绩
张三 语文 78 数学 87 英语 82 物理 90 期末考试 78+87+82+90
李四 语文 65 数学 0 英语 65 物理 85 期末考试 65+0+65+ 85
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int,[Time] varchar(20))
Insert Class
select N'张三',N'语文',78,'期末考试' union all
select N'张三',N'数学',87,'期末考试' union all
select N'张三',N'英语',82,'期末考试' union all
select N'张三',N'物理',90,'期末考试' union all
select N'李四',N'语文',65,'期末考试' union all
select N'李四',N'英语',65,'期末考试' union all
select N'李四',N'物理',85,'期末考试'
Go---select * from Class--用2000的方法(动态和静态两种方法得到如下结果该如何来写):要求: 没有考试的成绩记0分,课程是固定的
Student Course1 Score1 Course2 Score2 Course3 Score 3 Course4 Score4 Time 总成绩
张三 语文 78 数学 87 英语 82 物理 90 期末考试 78+87+82+90
李四 语文 65 数学 0 英语 65 物理 85 期末考试 65+0+65+ 85
drop table Class楼主这写法很强,学习了
Insert Class
select N'张三',N'语文',78,'期末考试' union all
select N'张三',N'数学',87,'期末考试' union all
select N'张三',N'英语',82,'期末考试' union all
select N'张三',N'物理',90,'期末考试' union all
select N'李四',N'语文',65,'期末考试' union all
select N'李四',N'英语',65,'期末考试' union all
select N'李四',N'物理',85,'期末考试'
Go declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case when Course='''+Course+''' then Course else '''+Course+''' end ) as ['+Course+'],'
+'max(case when Course='''+Course+''' then Score else 0 end ) as ['+Course+']'
from class group by Course
exec('select student,'+@sql+' from class group by student')
李四 数学 0 物理 85 英语 65 语文 65
张三 数学 87 物理 90 英语 82 语文 78
from class A left join class B on A.student =B.student
left join class C on A.student =C.student
left join class D on A.student =D.student
where A.course='语文' and B.Course='数学' and C.Course='英语' and D.course='物理'
,C.Course,isnull(C.score,0)
,D.Course,isnull(D.score,0),
A.score+B.scoure+C.score+D.score as '总成绩'
from class A left join class B on A.student =B.student
left join class C on A.student =C.student
left join class D on A.student =D.student
where A.course='语文' and B.Course='数学' and C.Course='英语' and D.course='物理'
if object_id('Class') is not null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int,[Time] varchar(20))
Insert Class
select N'张三',N'语文',78,'期末考试' union all
select N'张三',N'数学',87,'期末考试' union all
select N'张三',N'英语',82,'期末考试' union all
select N'张三',N'物理',90,'期末考试' union all
select N'李四',N'语文',65,'期末考试' union all
select N'李四',N'英语',65,'期末考试' union all
select N'李四',N'物理',85,'期末考试'
select student,course1='语文',语文 as score1,course2='数学',isnull(数学,0) score2,
course3='英语',英语 as score3,course4='物理',物理 as score4,time,总成绩 from
(select a.* ,b.总成绩 from
(select * from class a pivot (sum(score) for course in(语文,数学,英语,物理))b) a,
(select student,sum(score) 总成绩 FROM CLASS group by student) b where a.student=b.student) 表李四 语文 65 数学 0 英语 65 物理 85 期末考试 215
张三 语文 78 数学 87 英语 82 物理 90 期末考试 337
经典那就到精华里找呀
Student Course1 Score1 Course2 Score2 Course3 Score 3 Course4 Score4 Time 总成绩
if object_id('class') is not null
drop table class
go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int,[Time] varchar(20))
Insert Class
select N'张三',N'语文',78,'期末考试' union all
select N'张三',N'数学',87,'期末考试' union all
select N'张三',N'英语',82,'期末考试' union all
select N'张三',N'物理',90,'期末考试' union all
select N'李四',N'语文',65,'期末考试' union all
select N'李四',N'英语',65,'期末考试' union all
select N'李四',N'物理',85,'期末考试'
Go
--方法1:静态SQL
select Student,'语文' as course1,sum(case Course when '语文' then Score end) as score1,
'数学' as course2,sum(case Course when '数学' then Score end) as score2,
'英语 'as course3,sum(case Course when '英语' then Score end) as score3,
'物理' as course4,sum(case Course when '物理' then Score end) as score4,
Time,sum(score) as '总成绩'
from Class
group by TIme,student
order by student
--方法2:动态SQL
declare @a varchar(3000),@i int,@k int
select @a=''
select @k=0
select @i=(select count(distinct Course) from Class)
while @k<@i
begin
select @k=@k+1,@a =@a+','''+Course+''' as course'+cast(@k as varchar)+', sum (
case Course when '''+Course+''' then score end) as score'+cast(@k as varchar)
from Class group by Course
end
exec('select Student'+@a+',Time,sum(score) as ''总成绩'' from Class
group by TIme,student order by student')
/*
Student course1 score1 course2 score2 course3 score3 course4 score4 Time 总成绩
------- ------- ----------- ------- ----------- ------- ----------- ------- ----------- -------------------- -----------
李四 语文 65 数学 NULL 英语 65 物理 85 期末考试 215
张三 语文 78 数学 87 英语 82 物理 90 期末考试 337
(2 行受影响)Student course1 score1 course2 score2 course3 score3 course4 score4 Time 总成绩
------- ------- ----------- ------- ----------- ------- ----------- ------- ----------- -------------------- -----------
李四 数学 NULL 物理 85 英语 65 语文 65 期末考试 215
张三 数学 87 物理 90 英语 82 语文 78 期末考试 337*/
drop table class
go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int,[Time] varchar(20))
Insert Class
select N'张三',N'语文',78,'期末考试' union all
select N'张三',N'数学',87,'期末考试' union all
select N'张三',N'英语',82,'期末考试' union all
select N'张三',N'物理',90,'期末考试' union all
select N'李四',N'语文',65,'期末考试' union all
select N'李四',N'英语',65,'期末考试' union all
select N'李四',N'物理',85,'期末考试'
Go
--方法1:静态SQL
select Student,'语文' as course1,sum(case Course when '语文' then Score end) as score1,
'数学' as course2,sum(case Course when '数学' then Score end) as score2,
'英语 'as course3,sum(case Course when '英语' then Score end) as score3,
'物理' as course4,sum(case Course when '物理' then Score end) as score4,
Time,sum(score) as '总成绩'
from Class
group by TIme,student
order by student
--方法2:动态SQL
declare @a varchar(3000),@i int,@k int
select @a=''
select @k=0
select @i=(select count(distinct Course) from Class)
while @k<@i
begin
select @k=@k+1,@a =@a+','''+Course+''' as course'+cast(@k as varchar)+', sum (
case Course when '''+Course+''' then score end) as score'+cast(@k as varchar)
from Class group by Course
end
exec('select Student'+@a+',Time,sum(score) as ''总成绩'' from Class
group by TIme,student order by student')
/*
Student course1 score1 course2 score2 course3 score3 course4 score4 Time 总成绩
------- ------- ----------- ------- ----------- ------- ----------- ------- ----------- -------------------- -----------
李四 语文 65 数学 NULL 英语 65 物理 85 期末考试 215
张三 语文 78 数学 87 英语 82 物理 90 期末考试 337
(2 行受影响)Student course1 score1 course2 score2 course3 score3 course4 score4 Time 总成绩
------- ------- ----------- ------- ----------- ------- ----------- ------- ----------- -------------------- -----------
李四 数学 NULL 物理 85 英语 65 语文 65 期末考试 215
张三 数学 87 物理 90 英语 82 语文 78 期末考试 337*/null正合我意