SQL 行比较查询

Question

我是SQL新手，并尝试针对以下结果表编写查询，该查询将允许我列出学生Mary获得比Tom更高的科目。

    Subject        Student        Teacher        Score
    --------------------------------------------------
    Maths           Tom            Anderson        67
    Maths           Mary           Anderson        68
    English         Tom            Lewis           55
    English         Mary           Lewis           44
    French          Tom            Joubert         87
    French          Mary           Joubert         76
    Geography       Tom            Arnold          76
    Geography       Mary           Arnold          82

结果应为如下表：

    Subject     
    ---------
    Maths
    Geography

我相信我应该使用加入子句，但无法让它工作。

谢谢

Answer 1

假设没有重复的分数，您实际上可以通过聚合来执行此操作。

select subject
from results
group by subject
having max(case when student = 'Mary' then score end) >
       max(case when student = 'Tom' then score else 0 end);

请注意，这将包括玛丽有分数但汤姆没有的主题。

Answer 2

您可以使用自联接对此进行概括，如下所示：

SQL 小提琴

查询：

select a.Student As higher, b.Student as lower, a.Subject
  from score a
  join score b   ON a.Student <> b.Student
                AND a.Subject = b.Subject
                AND a.Score > b.Score

结果：

| HIGHER | LOWER |   SUBJECT |
|--------|-------|-----------|
|   Mary |   Tom |     Maths |
|    Tom |  Mary |   English |
|    Tom |  Mary |    French |
|   Mary |   Tom | Geography |

然后，显然，您可以通过添加此WHERE子句来过滤结果。

where a.student ='Mary' AND b.student = 'Tom

Answer 3

此查询将为您提供所需的结果：

SELECT subject
FROM   scores
WHERE  student = 'Mary'
AND    ( subject
       , score
       ) IN (
            SELECT   subject
            ,        MAX( score )
            FROM     scores
            GROUP BY subject
            );