mysql - 如何将表与其自身进行比较并在某一列中获取具有相同数据的行？

Question

我如何将表格与其自身进行比较，因为我需要检索在不同类(class)中获得相同成绩的学生的ID号，并显示他们的ID号、类(class)ID号和相同的成绩。我已经尝试过这段代码:

select sno,cno,score from subjcode where (select count(distinct score) from subjcode)>1;

但它不正确，因为输出应该是

S003    C001    81.90
S003    C002    81.90

仅

该表名为 subjcode:

sno     cno     score
S001    C001    78.90
S001    C002    82.90
S001    C003    59.00
S002    C001    80.90
S002    C002    72.90
S003    C001    81.90
S003    C002    81.90
S004    C001    60.90

感谢您的帮助!

Answer 1

只是一个内部连接，通过分数，您搜索的公共(public)字段。然后，您从中选择分数重复多次的不同行。应该是这样的。

select distinct A.cno, A.sno, B.score from subjcode A
inner join subjcode B on (B.score = A.score)
group by A.cno, A.sno
having count(B.score) > 1;