带分组的查询设计

Question

我无法针对这种情况提出有效的查询...

表格：

[ matchID ]  [ User ] [ team ]
     1          10       1
     1          77       2
     2          10       1
     2          77       1
     3          10       2
     4          10       1

注释：

• 有时对手是未知的，例如在比赛 2、3 和4
• 有时一个团队只有一个人，例如比赛 1
• 有时一个团队有 n 个人，例如比赛 2

期望的查询结果：

给定用户的 matchID 列表，其中对手未知。

我的第一次尝试是：

SELECT matchID FROM table
GROUP BY matchID
HAVING COUNT(matchID) = 1

但后来我意识到我没有统计有队友但对手未知的比赛，例如上面的比赛2。

用简单的英语来说，我需要获取所有 matchID，其中：

• 涉及给定用户 <- 编辑添加
• 每个人都在同一个团队中
• ，或者只列出一个人

，但我不知道如何在一个查询中有效地做到这一点。帮助？

Answer 1

您可以计算每场比赛的球队数量。如果只有一个那就不够了：

SELECT matchID 
FROM table
GROUP BY matchID 
HAVING COUNT(DISTINCT(team))=1;

Answer 2

您尚未提供表名称，因此我决定将其命名为 mut 作为 matches users team 的缩写。无论如何，这似乎适用于所有情况：

SELECT DISTINCT
    `matchID`
FROM
    `mut`
WHERE
    `user`=10
AND
    `matchID` NOT IN (
        SELECT
            `m1`.`matchID`
        FROM
            `mut` AS `m1`
        INNER JOIN
            `mut` AS `m2`
        ON
            `m1`.`matchID`=`m2`.`matchID`
        AND
            `m1`.`team`<>`m2`.`team`
    )

Answer 3

SELECT MatchID
  FROM Table
 GROUP BY MatchID
HAVING COUNT(DISTINCT Team) = 1;

您似乎需要那些表中只记录了一支球队的比赛，并且此查询为您提供了这些比赛。

如果匹配必须涉及特定用户（示例数据为 10），那么：

SELECT t1.MatchID
  FROM Table AS t1
 WHERE EXISTS(SELECT *
                FROM Table AS t2
               WHERE t2.UserID = 10 AND t1.MatchID = t2.MatchID)
 GROUP BY t1.MatchID
HAVING COUNT(DISTINCT t1.Team) = 1;

您可以将其作为（内部）自连接来执行：

SELECT t1.MatchID
  FROM Table AS t1
  JOIN Table AS t2 ON t1.MatchID = t2.MatchID AND t2.UserID = 10
 GROUP BY t1.MatchID
HAVING COUNT(DISTINCT t1.Team) = 1;

或者，也许更清楚一点：

SELECT t1.MatchID
  FROM Table AS t1
  JOIN (SELECT MatchID FROM Table AS t2 WHERE t2.UserID = 10) AS t2
    ON t1.MatchID = t2.MatchID
 GROUP BY t1.MatchID
HAVING COUNT(DISTINCT t1.Team) = 1;

子选择选择 UserID 10 的那些匹配玩过；其余的工作和以前一样。