作为别名加入的 sql 不起作用

Question

我是 sql 连接的新手..我有一个 5 岁的项目。现在我必须将它部署到一些新服务器上。所以将它部署到其他服务器上，我遇到了一些 sql 问题。这是 qyery 。

SELECT DISTINCT d.*,DATE_FORMAT(d.downloads_updated, '%c/%d/%Y') AS updated,DATE_FORMAT(d.downloads_created, '%c/%d/%Y') AS created, s2.strings_english as title, s2.strings_english as description 
FROM strings s, downloads d,
 products_has_downloads pd 
inner JOIN strings s2 ON d.downloads_description = s.strings_id WHERE d.downloads_id = pd.downloads_id AND s.strings_id = d.downloads_title AND d.downloads_status = 'Live' AND d.downloads_level = 'Public'
 ORDER BY d.downloads_updated DESC LIMIT 5

我遇到了这个错误

1054 - Unknown column 'd.downloads_description' in 'on clause'

我没有写过这个 sql 查询。它在旧服务器上运行良好。

我是刚加入数据库的新手。请问有人能帮帮我吗。

Answer 1

您混合了隐式和显式连接语法，这将不起作用。

您有以下隐式语法，其中表格由逗号连接:

FROM strings s, downloads d, products_has_downloads pd 

JOIN语法比逗号语法具有更高的优先级，因此 downloads 的别名在 ON 中不可用条款。

尝试使用所有相同的语法。我将您的查询更改为仅使用明确的 JOIN语法:

SELECT DISTINCT d.*,
  DATE_FORMAT(d.downloads_updated, '%c/%d/%Y') AS updated,
  DATE_FORMAT(d.downloads_created, '%c/%d/%Y') AS created, 
s2.strings_english as title, 
s2.strings_english as description 
FROM strings s
INNER JOIN downloads d
  ON d.downloads_description = s.strings_id
INNER JOIN products_has_downloads pd 
  on d.downloads_id = pd.downloads_id
inner JOIN strings s2 
  on s2.strings_id = d.downloads_title
WHERE d.downloads_status = 'Live' 
  AND d.downloads_level = 'Public'
ORDER BY d.downloads_updated DESC 
LIMIT 5