create table Student
(Id int,Namevarchar(10))
insert Studentselect1 ,'张三'union allselect2 ,'李四'
create table ScoreType(Idint,ScoreNamevarchar(10))
insert ScoreType--Id(主键) ScoreNameselect1 ,'语文'union allselect2 ,'数学'
createtable ScoreS(Idint,StudentIdint,ScoreTypeIdint,Score int)insert ScoreS--Id(主键) StudentId(Student外键) ScoreTypeId(ScoreType外键) Score
select1 ,1 ,1 ,80
union allselect2 ,1 ,2 ,85
union allselect3 ,2 ,1 ,75
union allselect4 ,2 ,2 ,90
select b.Name,
max(casewhen c.ScoreName='语文'then a.Scoreend) as '语文',
max(casewhen c.ScoreName='数学'then a.Scoreend) as '数学
'from Scores a,Student b,ScoreType c
where a.StudentId=b.idand a.ScoreTypeId=c.id
group by b.Name
/*Name 语文 数学---------- ----------- -----------李四 75 90张三 80 85警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)
比较常见的SQL多表查询行转列