MySQL - 错误代码 1054 - where 子句中的未知列 al.article_id

Question

我正在尝试执行以下查询，但无法找出问题所在。它一直在提示

Unknown column al.article_id in where clause

SELECT al.article_id, COUNT(1) 
FROM article_likes al
WHERE al.created_at > '2018-03-25 11:01:00' 
AND al.article_id 
    NOT IN (
        SELECT article_id 
        FROM article_views 
        WHERE user_id = 48 
    )
AND (
    NOT EXISTS (
        SELECT 1
        FROM article_ignored AS ai 
        WHERE ai.user_id = 48 
        AND ai.article_id = al.article_id
    )
    OR 
    (
        EXISTS 
        (
        SELECT 1 
        FROM article_ignored aii 
        WHERE aii.user_id = 48 
        AND aii.article_id = al.article_id
        AND created_at > '2018-03-26 11:01:00'
        )
        AND
        EXISTS
        (
            SELECT (
                IF(al.post_type = 'article',
                (
                    SELECT DISTINCT (a.tag_id) 
                    FROM 
                    (SELECT tag_id FROM article_tags WHERE article_id = al.article_id) a
                    JOIN 
                    (SELECT tag_id FROM user_tags WHERE user_id = 48 AND type = 'interest') b
                    ON a.tag_id = b.tag_id
                ),
                (
                    SELECT DISTINCT (a.tag_id) 
                    FROM 
                    (
                    SELECT tag_id FROM article_tags 
                    WHERE article_id = (SELECT id FROM articles WHERE post_type = 'question' AND aid = al.article_id)
                    ) a
                    JOIN 
                    (SELECT tag_id FROM user_tags WHERE user_id = 48 AND type = 'interest') b
                    ON a.tag_id = b.tag_id
                    )
                )
            )
        )
    )
)
GROUP BY article_id 
ORDER BY COUNT(1) DESC
LIMIT 3

错误在下面的部分，但我无法解决，请有人帮忙。这是因为它嵌套在 exists -> select -> if 中。

EXISTS
        (
            SELECT (
                IF(al.post_type = 'article',
                (
                    SELECT DISTINCT (a.tag_id) 
                    FROM 
                    (SELECT tag_id FROM article_tags WHERE article_id = al.article_id) a
                    JOIN 
                    (SELECT tag_id FROM user_tags WHERE user_id = 48 AND type = 'interest') b
                    ON a.tag_id = b.tag_id
                ),

Answer 1

我认为您可以尝试在此处指定特定的列“table.column”:

SELECT tag_id FROM article_tags WHERE article_id = al.article_id)

到:

SELECT tag_id FROM article_tags WHERE table.article_id = al.article_id)