请教一个多子查询问题

Question

mysql> select * from score;
+-----+-------+--------+
| SNO | CNO | DEGREE |
+-----+-------+--------+
| 103 | 3-245 | 86.0 |
| 105 | 3-245 | 75.0 |
| 109 | 3-245 | 68.0 |
| 103 | 3-105 | 92.0 |
| 105 | 3-105 | 88.0 |
| 109 | 3-105 | 76.0 |
| 101 | 3-105 | 64.0 |
| 107 | 3-105 | 91.0 |
| 108 | 3-105 | 78.0 |
| 101 | 6-166 | 85.0 |
| 107 | 6-106 | 79.0 |
| 108 | 6-166 | 81.0 |
+-----+-------+--------+
我需要 在这个表中 做这样一个查询
查询选修了 3-105和3-245并且3-105成绩高于3-245
的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

Answer 1

[code="sql"]select distinct s0.sno, s0.cno, s0.degree
from score s0, score s1, score s2
where s0.sno = s1.sno and s1.cno = '3-105'
and s0.sno = s2.sno and s2.cno = '3-324'
and s1.degree > s2.degree
group by s0.sno, s0.cno, s0.degree
order by s0.degree
[/code]