- 查询成绩比该课程平均成绩低的同学的成绩表
—复制表:由于同表同字段不能同时比较
#
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(