mysql查询（三）

查询同时存在 ‘A001‘和‘A002’课程情况

成绩表：ex_chengjibiao_95         

（1）先查询A001的课程

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

（2）在查询A002的课程

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

（3）两张表合并分别为a和b      最后where筛选

命令：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

 

（4）去除冗余（a表分数命名为01_score，b表分数命名为02_score）

命令：SELECT a.Sid,a.score 01_score,b.score 02_score 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