多表查询

Question

我有四个表：

• 角色
• arena_team
• arena_table_member
• arena_team_stats。

角色表有 guid，名称

arena_team 表有 arenateamid，名称，类型

arena_table_member 表有 guid（这与角色表中的相同），arenamid

arena_team_stats 表有 arenateamid，评级，胜利，wins2，玩过

我如何获取竞技场列表团队的性格在哪里？我尝试过：

$result=mysql_query("SELECT 
          characters.guid
           , characters.name
           , arena_team.arenateamid
           , arena_team.name
           , arena_team_stats.rating
           , arena_team_stats.wins
           , arena_team_stats.wins2
           , arena_team_stats.played
           , arena_team.type 
   FROM characters
           , arena_team_stats
           , arena_team 
    WHERE characters.name LIKE '%$q%' 
    AND arena_team.arenateamid = arena_team_stats.arenateamid 
    ORDER BY arena_team_stats.rating DESC") 
or die(mysql_error());

但它返回 arena_team 表中的所有竞技场球队。

Answer 1

您应该使用连接来代替。当您在 FROM 部分中使用逗号分隔表时，您将在结果中获得所有可能的组合。

尝试这个查询（使用联接）：

SELECT c.guid,c.name,a.arenateamid,a.name,
  ts.rating,ts.wins,ts.wins2,
  ts.played,a.type 
 FROM characters c
         JOIN arena_table_member ON arena_table_member.guid=character.guid
  JOIN arena_team a ON arena_table_member.arenateamid = a.arenateamid
  JOIN arena_team_stats ts ON ts.arenateamid = a.arenateamid
 WHERE c.name LIKE '%$q%' 
  AND a.arenateamid = ts.arenateamid 
 ORDER BY ts.rating DESC

有两件事：

1. 您应该在此处使用别名。 （上面的c、a、ts代替完整的表名称为别名）

2. 如果要与字符'?'精确匹配，请使用c.name = '?'。 c.name like '%?%' 将返回所有带有 ? 的 c.name在他们之中。

Answer 2

看来您缺少 JOIN。使用表别名和格式化更容易阅读：

    SELECT c.guid
         , c.name
         , at.arenateamid
         , at.name
         , at.type
         , ats.rating
         , ats.wins
         , ats.wins2 
         , ats.played
      FROM characters c
INNER JOIN arena_team_member atm ON atm.guid = c.guid
INNER JOIN arena_team at ON at.arenateamid = atm.arenateamid
INNER JOIN arena_team_stats ats ON ats.arenateamid = at.arenateamid
     WHERE c.name LIKE '%$q%'
  ORDER BY ats.rating DESC

Answer 3

SELECT Char.guid, Chars.name, Teams.arenateamid, Teams.name, Stats.rating,
       Stats.wins, Stats.wins2, Stats.played, Teams.type
FROM characters AS Chars,arena_team_stats AS Stats, arena_team AS Teams,
     arena_table_member AS Members
WHERE Chars.name LIKE '%$q%' AND Teams.arenateamid = Stats.arenateamid AND
      Members.guid = Chars.guid AND Members.arenateamid = Teams.arenateamid
ORDER BY Stats.rating DESC

您缺少一些表连接。