mysql - 将所有数据与特定选择的用户 ID 的数据进行比较？

Question

我有一个 mysql 表，用于保存团队比赛的数据。

目标:计算其他 SquadID 与 SquadID=21

共享相同 Team 值的次数

// Selections table
+--------+---------+------+
| GameID | SquadID | Team |
+--------+---------+------+
|      1 |       5 | A    |
|      1 |       7 | B    |
|      1 |      11 | A    |
|      1 |      21 | A    |
|      2 |       5 | A    |
|      2 |       7 | B    |
|      2 |      11 | A    |
|      2 |      21 | A    |
|      3 |       5 | A    |
|      3 |       7 | B    |
|      3 |      11 | A    |
|      3 |      21 | A    |
|      4 |       5 | A    |
|      4 |      11 | B    |
|      4 |      21 | A    |
|      5 |       5 | A    |
|      5 |      11 | B    |
|      5 |      21 | A    |
|      6 |       5 | A    |
|      6 |      11 | B    |
|      6 |      21 | A    |
+--------+---------+------+


// Desired Result
+---------+----------+
| SquadID | TeamMate |
+---------+----------+
|       5 |        6 |
|       7 |        0 |
|      11 |        3 |
|      21 |        6 |
+----------+---------+

我尝试使用子查询来指定我想要比较的特定玩家，并且由于该子查询有多行，所以我使用了 in 而不是 = .

// Current Query
SELECT
    SquadID, 
    COUNT(Team IN (SELECT Team FROM selections WHERE SquadID=21) AND GameID IN (SELECT GameID FROM selections WHERE SquadID=21)) AS TeamMate 
FROM      
    selections  
GROUP BY 
    SquadID;

我得到的结果是用户玩过的游戏数，而不是用户与 SquadID=21 属于同一团队的游戏数

// Current Result
+---------+----------+
| SquadID | TeamMate |
+---------+----------+
|       5 |        6 |
|       7 |        3 |
|      11 |        6 |
|      21 |        6 |
+---------+----------+

我错过了什么？

<小时/>

// DESCRIBE selections;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| GameID  | int(11) | NO   | PRI | 0       |       |
| SquadID | int(4)  | NO   | PRI | NULL    |       |
| Team    | char(1) | NO   |     | NULL    |       |
| TeamID  | int(11) | NO   |     | 1       |       |
+---------+---------+------+-----+---------+-------+

Answer 1

一般规则是避免嵌套选择并寻找更好的逻辑排列连接的方法。让我们看一下交叉连接:

From  selections s1
inner join selects s2 on s1.gameid = s2.gameid and s1.team = s2.team

这将生成与另一个小队 ID 一起参与的每个小队 ID 的交叉连接列表(即:他们在同一场比赛中并且在同一支球队中)。我们只对球队与 21 队一起参加比赛的时间感兴趣，因此添加一个 where 子句:

where s2.squadid = 21

然后只需选择您想要的字段/计数即可:

select s1.squad, count(1) as teammate

任何聚合都需要分组依据

group by s1.squad

将它们组合在一起并尝试一下。奇怪的是，这将产生一个列表，其中 21 队将全部显示为在自己的球队中比赛 6 次。添加 where 子句可以消除这种情况

where s1.squadid <> s2.squadid