行转列
select student.name,max(case when course.name='数学' then score end) "数学"
,max(case when course.name='语文' then score end) "语文"
,max(case when course.name='英语' then score end) "英语" from (
select '001'no,'数学' name from dual
union all
select '002', '语文' from dual
union all
select '003', '英语' from dual
)course,
(
select 'S001'no ,'小红' name from dual
union all
select 'S002','小明' from dual
) student
,(
select 'S001' studentno,'001' courseno,90 score from dual
union all
select 'S002','002',88 from dual
union all
select 'S001','003',70 from dual
)
where course.no= .courseno and student.no=.studentno
group by student.name
select student.name,max(case when course.name='数学' then score end) "数学"
,max(case when course.name='语文' then score end) "语文"
,max(case when course.name='英语' then score end) "英语" from (
select '001'no,'数学' name from dual
union all
select '002', '语文' from dual
union all
select '003', '英语' from dual
)course,
(
select 'S001'no ,'小红' name from dual
union all
select 'S002','小明' from dual
) student
,(
select 'S001' studentno,'001' courseno,90 score from dual
union all
select 'S002','002',88 from dual
union all
select 'S001','003',70 from dual
)
where course.no= .courseno and student.no=.studentno
group by student.name
如果第一张表里的里面的数据多了肿么搞。