mysql - 在 SQL INNER JOIN 中使用额外的 WHERE

Question

$sql = "SELECT events.*, leagues.lname as leagueName,leagues.aliasname as AliasName, t1.tname as team1_name, t2.tname as team2_name 
FROM events 
INNER JOIN leagues ON events.leagueid = leagues.id 
INNER JOIN teams AS t1 ON events.teamhid = t1.teamid 
INNER JOIN teams AS t2 ON events.teamaid = t2.teamid 
WHERE sdate='1352835000' ";

它给出了带有 COLUMNS 的 TABLE:

id sdate ... leagueName AliasName team1_name team2_name 

如何添加附加条件:

WHERE AliasName LIKE 'word' AND (team1_name LIKE 'word2' OR  team2_name LIKE 'word3')

Answer 1

你不能使用 ALIAS of column on (which is in-line with) where 子句，所以在你的情况下你需要使用 tableName.columnName(除了将其包装在子查询中)

WHERE leagues.aliasname LIKE 'word' AND 
     (t1.tname LIKE 'word2' OR  t2.tname LIKE 'word3')

之所以不能使用是因为查询的执行顺序如下

• FROM 子句(你可以在这里设置 TABLE 别名)
• WHERE 子句
• GROUP BY 子句
• HAVING子句
• SELECT 子句(此处为 COLUMN 别名)
• ORDER BY 子句