MySQL错误，但我前缀我的“ID”有鉴于查询表

问题描述：

“专栏from子句是模糊的‘身份证’”：MySQL错误，但我前缀我的“ID”有鉴于查询表

 
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]%')

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

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

答

这不是要帮助，如果你改变

INNER JOIN score AS score_1 USING (id)

到

INNER JOIN score AS score_1 (score_1.id_score = score.id)

是，INNER JOIN得分AS score_1 ON（score_1.id = score.id）已删除错误消息，但未给我任何结果。我想我需要使用子查询/视图，而不是将太多东西结合在一起。 – user113017 2009-12-03 15:44:36

答

在这种情况下我使用子查询成功地这样的：

 
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)

MySQL错误，但我前缀我的“ID”有鉴于查询表

相关推荐