sql-server - 在 SQL Server 中查找具有条件的同一个表上的值组合

Question

我有一个名为 PlayerScore 的表，其中包含玩家姓名及其平均得分:

Id  Name     Average
1   Sakib      80
2   Tamim      70
3   Mushfiq    60
4   Sabbir     50
5   Ashraful   20
6   Aftab      40
7   Rubel      30
8   Kalu       10

我想根据以下条件找到他们的合作伙伴组合:平均分数大于40的玩家不能与分数低于40的玩家搭档。我尝试了以下查询:

select a.Name,a.Average,b.Name,b.Average from ((select * from PlayerScore where Average<=40) as a inner join (select * from PlayerScore where Average<=40) as b on a.Id < b.Id) 

union 

select a.Name,a.Average,b.Name,b.Average from ((select * from PlayerScore where Average>=40) as a inner join (select * from PlayerScore where Average>=40) as b on a.Id < b.Id)

结果是:

Name    Average Name    Average
Aftab    40     Kalu    10
Aftab    40     Rubel   30
Ashraful 20     Aftab   40
Ashraful 20     Kalu    10
Ashraful 20     Rubel   30
Mushfiq  60     Aftab   40
Mushfiq  60     Sabbir  50
Rubel    30     Kalu    10
Sabbir   50     Aftab   40
Sakib    80     Aftab   40
Sakib    80     Mushfiq 60
Sakib    80     Sabbir  50
Sakib    80     Tamim   70
Tamim    70     Aftab   40
Tamim    70     Mushfiq 60
Tamim    70     Sabbir  50

他们有没有不使用UNION的解决方案

Answer 1

select distinct a.Name,a.Average,b.Name,b.Average 
from PlayerScore a 
join PlayerScore b 
  on a.Id < b.Id 
     and (    a.Average<=40 and b.Average<=40
           or a.Average>=40 and b.Average>=40
         )

它可能会产生相同的执行计划。