mysql - 在 WHERE 子句中使用 SELECT 子查询不起作用

Question

我正在尝试在 WHERE 子句中使用 SELECT 子查询的结果:

SELECT 
ID,
post_parent as parent,
post_status,
(SELECT post_status as p FROM wp_posts WHERE ID = parent) as parent_status
FROM wp_posts 
WHERE post_type = 'attachment' 
AND post_status = 'inherit' 
AND post_parent != 0 
AND parent_status = 'publish'
ORDER BY post_date DESC

但是MySQL似乎不知道parent_status列，错误是:

#1054 - Unknown column 'parent_status' in 'where clause'

但是，如果您删除 AND Parent_status = 'publish'，您将得到包含 Parent_status 列的结果:

ID      parent  post_status parent_status
1908    1904    inherit     publish
1907    1904    inherit     publish
1906    1904    inherit     publish
1905    1904    inherit     publish
1902    1900    inherit     publish
....

该查询的目标是在外部查询中获取 post_parent ID，然后在子查询中获取父查询的 post_status。这是 WordPress，我尝试仅在媒体附件有父级且该父级已发布时列出媒体附件。

尝试将 SELECT 子查询与 WHERE 子句一起使用时，我可能犯了一些根本性错误。任何提示表示赞赏。

Answer 1

不能在 where 子句中使用列别名。您有多种选择。在这种情况下，最好使用允许 having 子句中的逻辑的 MySQL 扩展。子查询中也存在逻辑问题。我认为你的意思是:

SELECT ID, post_parent as parent, post_status,
       (SELECT post_status FROM wp_posts wp2 WHERE wp2.ID = wp.post_parent) as parent_status
FROM wp_posts wp
WHERE post_type = 'attachment'  AND post_status = 'inherit'  AND post_parent <> 0 
HAVING parent_status = 'publish'
ORDER BY post_date DESC;