我有2张表,如下:
student
stu_id stu_name class_id class_name
1001 张建明 200205012 HBS-I-0602
1002 王楠 200408008 HBS-III-0503
1003 吴郝 200304015 HBS-II-0701
1004 邱源 200506023 HBS-I-0902score
cour_id cour_name score stu_id
C01 Java 89 1001
C02 Oracle 85.5 1001
C03 C++ 65 1003
C04 C# 78 1004需要执行按学号查询该学号的学生的课程名称和成绩,希望查询的结果合成一条记录,如下所示:
stu_name class_id class_name cour_name score cour_name score
张建明 200205012 HBS-I-0602 Java 89 Oracle 85.5sql语句应该怎么写?
请教各位大侠
student
stu_id stu_name class_id class_name
1001 张建明 200205012 HBS-I-0602
1002 王楠 200408008 HBS-III-0503
1003 吴郝 200304015 HBS-II-0701
1004 邱源 200506023 HBS-I-0902score
cour_id cour_name score stu_id
C01 Java 89 1001
C02 Oracle 85.5 1001
C03 C++ 65 1003
C04 C# 78 1004需要执行按学号查询该学号的学生的课程名称和成绩,希望查询的结果合成一条记录,如下所示:
stu_name class_id class_name cour_name score cour_name score
张建明 200205012 HBS-I-0602 Java 89 Oracle 85.5sql语句应该怎么写?
请教各位大侠
student JOIN score ON student.stu_id=score.stu_id
stu_name class_id class_name stu_id C# C++ Java Oracle
-------- --------- ----------- ------ ----- ----- ----- ------
张建明 200205012 HBS-I-0602 1001 NULL NULL 89.00 85.50
吴 郝 200304015 HBS-II-0701 1003 NULL 65.00 NULL NULL
邱 源 200506023 HBS-I-0902 1004 78.00 NULL NULL NULL
create table #t1
(
stu_id varchar(20),
stu_name varchar(20),
class_id varchar(20),
class_name varchar(20)
)
insert into #t1(stu_id,stu_name,class_id,class_name) values('1001','张建明','200205012','HBS-I-0602')
insert into #t1(stu_id,stu_name,class_id,class_name) values('1002','王 楠','200408008','HBS-III-0503')
insert into #t1(stu_id,stu_name,class_id,class_name) values('1003','吴 郝','200304015','HBS-II-0701')
insert into #t1(stu_id,stu_name,class_id,class_name) values('1004','邱 源','200506023','HBS-I-0902')create table #t2
(
cour_id varchar(10),
cour_name varchar(10),
score decimal(18,2),
stu_id varchar(10)
)insert into #t2(cour_id,cour_name,score,stu_id) values('C01','Java' ,89, '1001')
insert into #t2(cour_id,cour_name,score,stu_id) values('C02','Oracle',85.5,'1001')
insert into #t2(cour_id,cour_name,score,stu_id) values('C03','C++' ,65, '1003')
insert into #t2(cour_id,cour_name,score,stu_id) values('C04','C#' ,78, '1004')declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',[' + cour_name + '] = max(case cour_name when ''' + cour_name + ''' then score else null end)'
from #t2 group by cour_name order by cour_name
set @sql = 'select a.stu_name,a.class_id,a.class_name,b.* from #t1 as a,(select stu_id ' + @sql + ' from #t2 group by stu_id) as b where a.stu_id = b.stu_id'
EXEC(@sql)drop table #t1
drop table #t2
insert into Student
select '1001' , '张建明' , '200205012' , 'HBS-I-0602'
union all
select '1002', '王楠', '200408008', 'HBS-III-0503'
union all
select '1003', '吴郝', '200304015', 'HBS-II-0701'
union all
select '1004', '邱源', '200506023', 'HBS-I-0902'Create table Score(cour_id varchar(20),cour_name varchar(20),score decimal(10,2),stu_id varchar(10))
insert into Score
select 'C01', 'Java', 89, '1001'
union all
select 'C02', 'Oracle', 85.5, '1001'
union all
select 'C03', 'C++', 65 , '1003'
union all
select 'C04', 'C#', 78, '1004'
Declare @SQL varchar(8000)
select @SQL=''
select @SQL=@SQL+','+quotename(cour_name)+'=max(case cour_name when '''+cour_name +''' then score else 0 end)'
from Score
group by cour_name
select @SQL='select student.*'+@SQL+' from student ,score where student.stu_id=score.stu_id group by student.stu_id,student.stu_Name,student.class_id,student.class_name'
print @SQL
exec(@SQL)drop table student
drop table Score1001 张建明 200205012 HBS-I-0602 .00 .00 89.00 85.50
1003 吴郝 200304015 HBS-II-0701 .00 65.00 .00 .00
1004 邱源 200506023 HBS-I-0902 78.00 .00 .00 .00
set @sql = ''
SET @SQL='select a.stu_name,a.class_id,a.class_name '
SELECT @SQL= @SQL+ ',MIN(CASE WHEN cour_name = ''' + cour_name + ''' THEN cour_name END) [cour_name],
MIN(CASE WHEN score = ' + cast(score as varchar) + ' THEN score END) [score]' FROM #t2 group by cour_name, score
SET @SQL=@SQL+' FROM #t1 a,#t2 b where a.stu_id=b.stu_id group by a.stu_name,a.class_id,a.class_name'
EXEC (@SQL)上面能得到楼主要的结果,但不如 dawugui(潇洒老乌龟) 的结果整齐