select m.*,n.course , o.maths score n.tname
from Studentinfo m ,  Teacherinfo n , Coursescore o 
where m.studentid = n.studentid and m.studentid = o.studentid 
union all
select m.*,n.course , o.english score n.tname
from Studentinfo m ,  Teacherinfo n , Coursescore o 
where m.studentid = n.studentid and m.studentid = o.studentid

------解决方案--------------------
SELECT A.*,c.course,b.maths,c.tname
FROM studentinfo A,coursescore b,teacherinfo c
where a.studentid=b.studentid and c.course='maths'
UNION ALL
SELECT A.*,c.course,b.english,c.tname
FROM studentinfo A,coursescore b,teacherinfo c
where a.studentid=b.studentid and c.course='english'

这个Coursescore表结构设计得不合理..

------解决方案--------------------
SQL code
create table Studentinfo(studentid varchar(10),name varchar(10),sex varchar(10))
insert into Studentinfo values('001', '张三', '男')
insert into Studentinfo values('002', '赵四', '女')
create table Coursescore(Studentid varchar(10),maths int,english int)
insert into Coursescore values('001', 80 ,90)
insert into Coursescore values('002', 75 ,95)
create table Teacherinfo(teacherid varchar(10),tname varchar(10),course varchar(10))
insert into Teacherinfo values('101', '美女', 'maths')
insert into Teacherinfo values('102', '帅哥', 'english')
go

select m.*,n.course , o.maths score ,n.tname
from Studentinfo m ,  Teacherinfo n , Coursescore o 
where m.studentid = o.studentid and n.course = 'maths'
union all
select m.*,n.course , o.english score ,n.tname
from Studentinfo m ,  Teacherinfo n , Coursescore o 
where m.studentid = o.studentid and n.course = 'english'


drop table Studentinfo , Coursescore ,Teacherinfo

/*
studentid  name       sex        course     score       tname      
---------- ---------- ---------- ---------- ----------- ---------- 
001        张三         男          maths      80          美女
002        赵四         女          maths      75          美女
001        张三         男          english    90          帅哥
002        赵四         女          english    95          帅哥

（所影响的行数为 4 行）

*/

------解决方案--------------------
SQL code
with Studentinfo as(
  select '001' studentid,'张三' name,'男' sex from dual union                 
  select '002' studentid,'赵四' name,'女' sex from dual),
Coursescore as(                 
  select '001' studentid, 80 maths, 90 english from dual union
  select '002' studentid, 75 maths, 95 english from dual),                 
Teacherinfo as(
  select '101' teacherid,'美女' tname, 'maths'   course from dual union                 
  select '102' teacherid,'帅哥' tname, 'english' course from dual)

SELECT s.Studentid, s.name, s.sex, c.course, c.score, t.tname
  FROM Studentinfo s,
       (SELECT studentid, 'maths' course, maths score
          FROM Coursescore
        UNION ALL
        SELECT studentid, 'english' course, english score FROM Coursescore) c,
       Teacherinfo t
 WHERE s.studentid = c.studentid AND
       c.course = t.course;

------解决方案--------------------
用哥这个笛卡尔积的：
SQL code

SQL> select s.studentid, s.name, s.sex, t.course,
  2         (select case when t.course = 'maths'     then maths
  3                      when t.course = 'english'   then english
  4