主页 / dba / 问题 / 123432
Accepted
mesqueeb
Asked: 2015-12-11 21:53:48 +0800 CST

连接表上无法解决的歧义问题

  • 772

我已经搜索了所有答案，但没有任何效果。不知何故，这个查询给了我歧义错误或“ID”：

SELECT wpp.ID AS theid, s.ID AS supplierid, post_title, wp_terms.name AS category 
FROM wp_posts AS wpp
LEFT JOIN wp_term_relationships ON wpp.ID = object_id
LEFT JOIN wp_terms ON term_taxonomy_id = term_id
LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID
WHERE post_type = 'post'
AND (post_title LIKE '%bag%'
OR wp_terms.name LIKE '%bag%'
OR post_content LIKE '%bag%')
GROUP BY ID

问题出在我添加这一行时：

LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID

但我看不出我做错了什么......有什么提示吗？

mysql

1 个回答

  1. Best Answer

    GROUP BY ID. 这就是问题所在。MySQL 无法猜测该列ID属于哪个表。ID添加的wp_teleapo_supplier表和其他 3 个表中的一个（我猜是那个）中都有一列wp_posts。

    所有列引用都应以表名（或别名）为前缀：( object_id, term_id, post_type, post_title, post_content, ID)。这些其他引用没有产生此错误的唯一原因是你很幸运，它们只出现在一个表中，来自FROM子句的 4 个表。

    （一些前缀可能是错误的。但如果它们是错误的，你会得到一个错误，你可以更正它们:)

    SELECT wpp.ID AS theid, s.ID AS supplierid, wpp.post_title, t.name AS category 
FROM wp_posts AS wpp
  LEFT JOIN wp_term_relationships AS r ON wpp.ID = r.object_id
  LEFT JOIN wp_terms AS t ON term_taxonomy_id = t.term_id
  LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID
WHERE wpp.post_type = 'post'
  AND (wpp.post_title LIKE '%bag%'
    OR t.name LIKE '%bag%'
    OR wpp.post_content LIKE '%bag%')
GROUP BY wpp.ID ;
    • 4

相关问题