mysql 查看 sid_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