多表关联查询问题：按球队所属城市筛选最快球员（排除Devify队）

让我们一步步找出原查询的问题并修正：

原查询的核心错误 #

1. 城市关联逻辑错误
   你写的inner join towns as tt on p.team_id = tt.id完全不对——球员的球队ID怎么会等于城市ID呢？球队和城市的关联应该是通过球队的场馆（如果有stadiums表）或者球队直接绑定的城市外键（比如teams.town_id），这是导致结果城市名称不对的主要原因。

2. 分组字段错误
   原查询按s.id（技能数据的ID）分组，这会让每个技能记录单独成组，根本达不到“按城市找最快球员”的目的，我们需要按城市的唯一标识（ID）和名称来分组。

3. JOIN类型不合理
   原查询用RIGHT JOIN skills_data，这会过滤掉没有技能数据的球员，但你的预期结果里包含NULL的情况，所以应该用LEFT JOIN从球员表关联技能表，保留所有符合条件的球员。

4. 模糊匹配不必要
   t.name not like 'Devify'用了模糊匹配，但我们需要精确排除'Devify'队，直接用t.name != 'Devify'更准确高效。

修正后的SQL语句 #

假设你的数据库里有stadiums表（结构大概是stadiums: id, name, town_id，用来关联球队和城市），修正后的查询如下：

SELECT 
    MAX(s.speed) AS max_speed,
    tt.name AS town_name
FROM players p
LEFT JOIN skills_data s ON p.skills_data_id = s.id
JOIN teams t ON p.team_id = t.id
JOIN stadiums st ON t.stadium_id = st.id
JOIN towns tt ON st.town_id = tt.id
WHERE t.name != 'Devify'
GROUP BY tt.id, tt.name
ORDER BY max_speed DESC, town_name;

如果你的teams表直接有town_id字段（绑定到towns.id），可以简化成：

SELECT 
    MAX(s.speed) AS max_speed,
    tt.name AS town_name
FROM players p
LEFT JOIN skills_data s ON p.skills_data_id = s.id
JOIN teams t ON p.team_id = t.id
JOIN towns tt ON t.town_id = tt.id
WHERE t.name != 'Devify'
GROUP BY tt.id, tt.name
ORDER BY max_speed DESC, town_name;

为什么这样改？ #

• LEFT JOIN skills_data确保没有技能数据的球员（speed为NULL）也会被包含，符合你的预期结果。
• 正确的关联逻辑让我们能准确获取球队所属的城市。
• 按城市ID和名称分组，保证每个城市只返回一条最大速度的记录。
• 精确的条件排除了'Devify'队的所有球员。

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