mysql查询（二）

1.查询" A001 "课程比" A002 "课程成绩高的学生的信息及课程分数

学生表：ex_xueshengbiao_95            成绩表：ex_chengjibiao_95

（1）先查询A001的课程分数

命令：SELECT * from ex_chengjibiao_95 WHERE CId='A001'

 

（2）再查询A002的课程分数

命令：SELECT * from ex_chengjibiao_95 WHERE CId='A002'

（3）将这两个查询出来的表重命名 （as）a和b

命令：  SELECT* FROM

(SELECT * from ex_chengjibiao_95 WHERE CId='A001')as a,

(SELECT * from ex_chengjibiao_95 WHERE CId='A002')as b

 

（4）加上一个条件WHERE (查询a表的Sid b的Sid 和分数)

命令：SELECT* FROM

(SELECT * from ex_chengjibiao_95 WHERE CId='A001')as a,

(SELECT * from ex_chengjibiao_95 WHERE CId='A002')as b

WHERE a.SId=b.SId AND a.score>b.score

 

（5）将a表的Sid和score命名为class1    将b表的score命名为class2

命令：SELECT a.Sid,a.score class1,b.score class2 FROM

(SELECT * from ex_chengjibiao_95 WHERE CId='A001')as a,

(SELECT * from ex_chengjibiao_95 WHERE CId='A002')as b

WHERE a.SId=b.SId AND a.score>b.score

 

（6）将上次查询的出来的表命名为r 然后和学生表的Sid和r表的Sid一起查询

命令：select * from ex_xueshengbiao_95 right join

(SELECT a.Sid,a.score class1,b.score class2 FROM

(SELECT * from ex_chengjibiao_95 WHERE CId='A001')as a,

(SELECT * from ex_chengjibiao_95 WHERE CId='A002')as b

WHERE a.SId=b.SId AND a.score>b.score)r

on ex_xueshengbiao_95.Sid=r.Sid