select Name,
[语文]=sum(case when SUBJECTNAME='语文' then Score else 0 end),
[数学]=sum(case when SUBJECTNAME='数学' then Score else 0 end),
[英语]=sum(case when SUBJECTNAME='英语' then Score else 0 end)
from
(
select A.*,
B.SUBJECTNAME,
[Score]=isnull(C.SCORE,0)
from 学生表 A
left join 科目表 B on 1>0
left join 成绩表 C on B.SUBJECTID=C.SUBJECTID
and
A.STUDENTID=C.STUDENTID
)t
group by Name
,CASE SUBJECTID WHEN 1 THEN SCORE ELSE 0 END AS 数学
,CASE SUBJECTID WHEN 2 THEN SCORE ELSE 0 END AS 语文
,CASE SUBJECTID WHEN 3 THEN SCORE ELSE 0 END AS 英语
FROM 学生表 A LEFT JOIN 成绩表 B
ON A.STUDENTID=B.STUDENTID
select A.*,
B.SUBJECTNAME,
[Score]=isnull(C.SCORE,0)
into #
from 学生表 A
left join 科目表 B on 1>0
left join 成绩表 C on B.SUBJECTID=C.SUBJECTID
and
A.STUDENTID=C.STUDENTID
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+SUBJECTNAME+']=sum(case when SUBJECTNAME='''+SUBJECTNAME+''' then Score else 0 end)'
from 科目表
set @sql='select Name'+@sql+' from # group by Name'
exec(@sql)
(
STUDENTID int,Name varchar(20)
)
create table [科目表]
(
SUBJECTID int,SUBJECTNAME varchar(20)
)
create table [成绩表]
(
ID int, STUDENTID int,SUBJECTID int,Score int
)
insert [学生表]
select 1,'张三' union
select 2,'李四' union
select 3,'王五'
insert [科目表]
select 1,'语文' union
select 2,'数学' union
select 3,'英语'
insert [成绩表]
select 1,2,2,80--一般查询
select Name,
[语文]=sum(case when SUBJECTNAME='语文' then Score else 0 end),
[数学]=sum(case when SUBJECTNAME='数学' then Score else 0 end),
[英语]=sum(case when SUBJECTNAME='英语' then Score else 0 end)
from
(
select A.*,
B.SUBJECTNAME,
[Score]=isnull(C.SCORE,0)
from [学生表] A
left join [科目表] B on 1>0
left join [成绩表] C on B.SUBJECTID=C.SUBJECTID
and
A.STUDENTID=C.STUDENTID
)t
group by Name--动态查询
select A.*,
B.SUBJECTNAME,
[Score]=isnull(C.SCORE,0)
into #
from 学生表 A
left join 科目表 B on 1>0
left join 成绩表 C on B.SUBJECTID=C.SUBJECTID
and
A.STUDENTID=C.STUDENTID
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+SUBJECTNAME+']=sum(case when SUBJECTNAME='''+SUBJECTNAME+''' then Score else 0 end)'
from 科目表
set @sql='select Name'+@sql+' from # group by Name'
exec(@sql)--删除测试环境
drop table #
drop table [学生表],[成绩表],[科目表]
--结果
/*
Name 语文 数学 英语
-------------------- ----------- ----------- -----------
李四 0 80 0
王五 0 0 0
张三 0 0 0(3 row(s) affected)Name 语文 数学 英语
-------------------- ----------- ----------- -----------
李四 0 80 0
王五 0 0 0
张三 0 0 0*/
create table student(STUDENTID int,NAME varchar(10))
create table course(SUBJECTID int,SUBJECTNAME varchar(10))
create table score(ID int,STUDENTID int,SUBJECTID int,SCORE int)
go
insert student select 1,'张三' union all select 2,'李四'
union all select 3,'王五'
insert course select 1,'语文' union all select 2,'数学'
union all select 3,'英语'
insert score select 1, 2, 2, 80
--查询
declare @str varchar(8000)
set @str=''
select @str=@str+' ,['+SUBJECTNAME
+']=max(case SUBJECTNAME when '''
+SUBJECTNAME+''' then score else 0 end)'
from course
set @str='select name '+@str
+'from(select t1.name, t3.SUBJECTNAME, t2.score
from student as t1 left join score as t2 on t1.STUDENTID=t2.STUDENTID
join course as t3 on t2.SUBJECTID=t3.SUBJECTID)tt
group by name '
exec(@str)
--清除
drop table student
drop table course
drop table score