表的结构和要求如下：
表名：Studentinfo：
studentid name sex
001 张三 男
002 赵四 女
表名：Coursescore
Studentid maths english
001 80 90
002 75 95
表名：Teacherinfo
teacherid tname course
101 美女 maths
102 帅哥 english
想要的结果：请各位指导SQL如何写：
Studentid name sex course score tname
001 张三 男 maths 80 美女
001 张三 男 english 90 帅哥
002 赵四 女 maths 75 美女
002 赵四 女 english 95 帅哥
------解决方案--------------------
- SQL code
select m.*,n.course , o.maths score n.tnamefrom Studentinfo m , Teacherinfo n , Coursescore o where m.studentid = n.studentid and m.studentid = o.studentid union allselect m.*,n.course , o.english score n.tnamefrom 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')goselect m.*,n.course , o.maths score ,n.tnamefrom Studentinfo m , Teacherinfo n , Coursescore o where m.studentid = o.studentid and n.course = 'maths'union allselect m.*,n.course , o.english score ,n.tnamefrom 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 else 0 5 end 6 from Coursescore c 7 where c.studentid = s.studentid 8 ) score, 9 t.tname 10 from Studentinfo s, Teacherinfo t 11 / STUDENTID NAME SEX COURSE SCORE TNAME---------- ---------- ---------- ---------- ---------- ----------001 张三 男 maths 80 美女001 张三 男 english 90 帅哥002 赵四 女 maths 75 美女002 赵四 女 english 95 帅哥