PHP MySQL - IN/ALL/ANY 子查询中的列 'id' 不明确

Question

我目前有以下查询工作:

$sql = $db->Query("SELECT a.id FROM surf a LEFT JOIN users b ON b.id = a.user LEFT JOIN surfed c ON c.user = '".$data['id']."' AND c.site = a.id WHERE a.active = '0' AND (b.coins >= a.cpc AND a.cpc >= '2') AND (c.site IS NULL AND a.user !='".$data['id']."')".$dbt_value." ORDER BY a.cpc DESC LIMIT 1");

但我想在其中添加以下 AND 语句:

 AND `cpc`<='10' AND (`id` NOT IN (SELECT `site` FROM `surfed_site`)) 

所以我让它看起来像:

$sql = $db->Query("SELECT a.id FROM surf a LEFT JOIN users b ON b.id = a.user LEFT JOIN surfed c ON c.user = '".$data['id']."' AND c.site = a.id WHERE a.active = '0' AND (b.coins >= a.cpc AND a.cpc >= '2') AND `cpc`<='10' AND (`id` NOT IN (SELECT `site` FROM `surfed_site`)) AND (c.site IS NULL AND a.user !='".$data['id']."')".$dbt_value." ORDER BY a.cpc DESC LIMIT 1");

但随后我收到此消息:

IN/ALL/ANY 子查询中的“id”列不明确

我的查询有什么问题？

Answer 1

尝试改变

AND `cpc`<='10' AND (`id` NOT IN (SELECT `site` FROM `surfed_site`)) 

到

AND `cpc`<='10' AND (`a.id` NOT IN (SELECT `site` FROM `surfed_site`)) 

您需要 a.id，因为它不确定要引用哪个表中的哪个 id 列。