mysql - 根据输出限制查询

Question

我正在为体育赛事编写代码，其中查询返回失球最少的人。

SELECT t.user AS userid, t.team AS teamid, dteam.name AS teamname, l.id AS leagueid, l.name AS leaguename, l.season AS leagueseason, l.career AS careerid
FROM tbl_foot_tables t
INNER JOIN tbl_foot_career_teams team ON t.team = team.id
INNER JOIN tbl_foot_career_db_teams dteam ON dteam.id = team.teamid
INNER JOIN tbl_foot_leagues l ON l.id = t.league
WHERE t.league = 263
ORDER BY t.home_goals_against + t.away_goals_against LIMIT 1

我限制为 1，因为我想要失球最少的球员。然而，在 2/3/4 名球员都失球数相同的情况下，我需要它返回所有这些行。

如果我不限制，我将返回所有行，即使失球数不同。

我不知道在这种情况下该怎么办，因为我以前从未遇到过。

如有任何帮助，我们将不胜感激:)

Answer 1

您可以通过计算子查询中的最佳目标并使用 join 获取值来实现此目的。然后选择与此匹配的所有团队:

SELECT t.user AS userid, t.team AS teamid, dteam.name AS teamname, l.id AS leagueid,
       l.name AS leaguename, l.season AS leagueseason, l.career AS careerid
FROM tbl_foot_tables t INNER JOIN
     tbl_foot_career_teams team
     ON t.team = team.id INNER JOIN
     tbl_foot_career_db_teams dteam
     ON dteam.id = team.teamid INNER JOIN
     tbl_foot_leagues l
     ON l.id = t.league join
     (select t.home_goals_against + t.away_goals_against as goals
      from tbl_foot_tables t
      WHERE t.league = 263
      order by  t.home_goals_against + t.away_goals_against desc
      limit 1
     ) as thebest
     on  t.home_goals_against + t.away_goals_against = thebest.goals
WHERE t.league = 263 ;