mysql - 将连接结果与至少函数组合

Question

我是 SQL 的新手，已经设法足够熟练地掌握了基本功能，但是我现在正试图从内部联接的结果中找到至少有两个标记的人:

SELECT
    users.[First Name],
    users.[Last Name],
    IssuedTokens.UserID,
    IssuedTokens.TokenID,
    Tokens.TokenType
FROM IssuedTokens
INNER JOIN users ON users.ID = IssuedTokens.UserID
INNER JOIN Tokens ON Tokens.number = IssuedTokens.TokenID
GROUP BY IssuedTokens.UserID
HAVING COUNT(*) >= 2
ORDER BY IssuedTokens.UserID

这给出了错误:

Column 'Users.First Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我很乐意在预先存在的表上使用函数，但还没有看到如何操作连接的结果。如果有人可以提供帮助，我们将不胜感激。

Answer 1

您可以在 join 之前进行单独的聚合，以获得具有多个 token 的用户。然后，查询的其余部分不需要聚合:

SELECT u.[First Name], u.[Last Name], u.UserID, it.TokenID, t.TokenType
FROM IssuedTokens it INNER JOIN
     users u
     ON u.ID = it.UserID INNER JOIN
     Tokens t
     ON t.number = it.TokenID INNER JOIN
     (SELECT it.UserId
      FROM IssuedTokens it
      GROUP BY it.UserId
      HAVING COUNT(*) >= 2
     ) itu
     ON itu.UserId = it.UserId
ORDER BY it.UserID;