SQL 查询 - 比较不同项目上的两个项目

您好，我是 SQL 代码的新手，想知道是否可以获得以下输出：-

**EVT   John    Paul    Difference**
A1      1       2       -1
A2      2       3       -1
  

来自以下数据源。

**EVT   PERS    RANK**
A1      John    1
A1      Paul    2
A1      Ringo   3
A1      George  4
A2      Ringo   1
A2      John    2
A2      Paul    3
A2      George  4
  

SELECT 
      EVT,
      MAX(CASE WHEN Pers='John' THEN Rank END) AS John,
      MAX(CASE WHEN Pers='Paul' THEN Rank END) AS Paul,
      MAX(CASE WHEN Pers='John' THEN Rank END) -  
                  MAX(CASE WHEN Pers='Paul' THEN Rank END) as Difference
FROM YourTable 
WHERE Pers IN ('John','Paul')
GROUP BY EVT