mysql 复制数据表 等级查询(rank) 连接查询(join)

31. 查询成绩比该课程平均成绩低的同学的成绩表
    —复制表：由于同表同字段不能同时比较

# 
select * from score a 
where sc_degree < 
(select avg(sc_degree) from score b where a.c_no = b.c_no);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
| 109  | 6-166 |        81 |
+------+-------+-----------+

rank查询

# grade表
CREATE TABLE grade(
    low INT(3),
    upp INT(3),
    grade CHAR(1)
);
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(