不是已经回过了吗?select b.xh,c.cname,b.,b.sno from course c inner join ( select b1.xh,b1.,count(*) as sno from record b1 left join record b2 on b1.cno=b2.cno and b1.<b2. ) b on c.cno=b.cno
你的回复没怎么看懂 里面的b b1 b2不知道怎么来的 虚表?
b b1 b2是别名,分别 record b1 , record b2 也就是b1,b2都是 record表。b 是指整个( select b1.xh,b1.,count(*) as sno from record b1 leftjoin record b2 on b1.cno=b2.cno and b1.<b2.)
from course c inner join (
select b1.xh,b1.,count(*) as sno
from record b1 left join record b2 on b1.cno=b2.cno and b1.<b2.
) b on c.cno=b.cno
from record b1 leftjoin record b2 on b1.cno=b2.cno and b1.<b2.)
xh cno xueqi cno cname cvalue
01 106 90 2 106 数据库 5
02 106 80 2 105 C++ 2
03 105 80 2
01 105 52 2
02 105 85 2
希望得到xh为01的学生的第二学期各项课程成绩排名
课程 成绩 排名
+------+------+------+-------+
| xh | cno | | xueqi |
+------+------+------+-------+
| 01 | 106 | 90 | 2 |
| 02 | 106 | 80 | 2 |
| 03 | 105 | 80 | 2 |
| 01 | 105 | 52 | 2 |
| 02 | 105 | 85 | 2 |
+------+------+------+-------+
5 rows in set (0.00 sec)mysql> select c.cname,b1.,(select count(*) from record where cno=b1.cno and >b1.)+1 as 排名
-> from record b1 inner join course c on b1.cno=c.cno
-> where xh='01';
+--------+------+------+
| cname | | 排名 |
+--------+------+------+
| 数据库 | 90 | 1 |
| C++ | 52 | 3 |
+--------+------+------+
2 rows in set (0.00 sec)