首页 文章

MySQL错误“from _ clause中的列'id'是不明确的”，但我在表格中加上了我的“id”前缀

提问于
浏览 次
0

给出查询：

SELECT
 DISTINCT score.*
FROM score
LEFT OUTER JOIN tag ON (tag.id_score = score.id)
INNER JOIN score AS score_1 USING (id)
WHERE
 (score.song_name LIKE '%[Upload]%' 
  OR score.artist_name LIKE '%[Upload]%' 
  OR score.creator_name LIKE '%[Upload]%' 
  OR tag.name LIKE '%[Upload]%')
 AND (score_1.song_name LIKE '%[Check OK]%' 
  OR score_1.artist_name LIKE '%[Check OK]%' 
  OR score_1.creator_name LIKE '%[Check OK]%' 
  OR tag.name LIKE '%[Check OK]%')

我在MySQL 5.1.37中得到 Column 'id' in from clause is ambiguous . 通常人们通过在其模糊列前面添加一个显式表来解决这个问题，但在这里我已经这样做了： (tag.id_score = score.id) . 离开 LEFT OUTER JOIN tag 修复了问题，但不允许在标签表内搜索 .

这是MySQL中的错误还是我错过了什么？

mysql

2 回答

  • 0

    在这种情况下，我已成功使用子查询：

    SELECT * FROM ( SELECT
    DISTINCT score.*
FROM score
LEFT OUTER JOIN tag ON (tag.id_score = score.id)
WHERE
    (score.song_name LIKE '%[Upload]%' 
        OR score.artist_name LIKE '%[Upload]%' 
        OR score.creator_name LIKE '%[Upload]%' 
        OR tag.name LIKE '%[Upload]%')


) AS score_0 INNER JOIN ( SELECT
    DISTINCT score.*
FROM score
LEFT OUTER JOIN tag ON (tag.id_score = score.id)
WHERE
    (score.song_name LIKE '%[Check OK]%' 
        OR score.artist_name LIKE '%[Check OK]%' 
        OR score.creator_name LIKE '%[Check OK]%' 
        OR tag.name LIKE '%[Check OK]%')


) AS score_1 USING (id)
    回复于
  • 2

    如果你改变会不会有帮助

    INNER JOIN score AS score_1 USING (id)

    至

    INNER JOIN score AS score_1 (score_1.id_score = score.id)
    回复于

相关问题