如何在SQL中基于行列对比计算特定成员的条件平均值

我来帮你理清这个针对member2计算定制化平均值的需求，先明确规则，再结合输入输出示例拆解计算逻辑：

针对每条记录的member2，计算平均值时需要：

1. 找到该成员与自己搭档的所有记录（即member1和member2相同的条目）
2. 找到该成员与当前记录的member1之外的其他成员搭档的所有记录（无论该成员是member1还是member2）
3. 将上述两类记录的score求和，再除以符合条件的唯一ID数量，得到平均值
4. 特殊情况：如果该成员只有与自己搭档的记录，则直接用当前score作为平均值

输入数据集 #

+----+---------+---------+-------+
| ID | member1 | member2 | score |
+----+---------+---------+-------+
| 1  | Anna    | Sam     | 10    |
| 2  | Sam     | Sam     | 30    |
| 3  | Sam     | Nihal   | 40    |
| 4  | Nihal   | Sam     | 50    |
| 5  | Sam     | Anna    | 20    |
| 6  | Anna    | Anna    | 60    |
| 7  | Nihal   | May     | 70    |
| 8  | May     | May     | 80    |
+----+---------+---------+-------+

输出数据集（含计算注释） #

+----+---------+---------+-------+-----+
| ID | member1 | member2 | score | AVG |
+----+---------+---------+-------+-----+
| 1  | Anna    | Sam     | 10    | 40  | --> AVG= (30+40+50)/3
| 2  | Sam     | Sam     | 30    | 30  | --> AVG= score（仅自身搭档记录）
| 3  | Sam     | Nihal   | 40    | 70  | --> AVG= 70/1
| 4  | Nihal   | Sam     | 50    | 20  | --> AVG= (30+10+20)/3
| 5  | Sam     | Anna    | 20    | 60  | --> AVG= 60/1
| 6  | Anna    | Anna    | 60    | 60  | --> AVG= score（仅自身搭档记录）
| 7  | Nihal   | May     | 70    | 80  | --> AVG= 80/1
| 8  | May     | May     | 80    | 80  | --> AVG= score（仅自身搭档记录）
+----+---------+---------+-------+-----+

关键计算逻辑拆解 #

我们逐个分析输出中AVG的由来，确保规则落地：

• ID1（member2=Sam）：当前member1是Anna，所以排除Sam与Anna的搭档记录（ID1、ID5），取Sam与自己（ID2）、Sam与Nihal（ID3）、Nihal与Sam（ID4）的score求和：30+40+50=120，除以3条记录，得到40。
• ID2（member2=Sam）：仅存在Sam与自己搭档的记录，直接取当前score30作为AVG。
• ID3（member2=Nihal）：当前member1是Sam，排除Nihal与Sam的搭档记录（ID3），取Nihal与May的记录（ID7）的score70，除以1条记录，得到70。
• ID4（member2=Sam）：当前member1是Nihal，排除Sam与Nihal的搭档记录（ID3、ID4），取Sam与自己（ID2）、Anna与Sam（ID1）、Sam与Anna（ID5）的score求和：30+10+20=60，除以3条记录，得到20。
• ID5（member2=Anna）：当前member1是Sam，排除Anna与Sam的搭档记录（ID1、ID5），取Anna与自己的记录（ID6）的score60，除以1条记录，得到60。
• ID6（member2=Anna）：仅存在Anna与自己搭档的记录，直接取当前score60作为AVG。
• ID7（member2=May）：当前member1是Nihal，排除May与Nihal的搭档记录（ID7），取May与自己的记录（ID8）的score80，除以1条记录，得到80。
• ID8（member2=May）：仅存在May与自己搭档的记录，直接取当前score80作为AVG。

内容的提问来源于stack exchange，提问作者sara