MySQL - 同一查询中的 COUNT 和 MAX。 MAX 忽略 WHERE 子句

Question

我正在执行以下脚本:

SELECT  a.*,
    gg.image as 'recent_image_file' 
FROM 

(   SELECT  c.cid, 
            c.category_name AS 'cat_name',
            COUNT(g.id) AS image_count,
            c.category_image AS 'images',
            c.estgendpref,
            MAX(n.id) as 'recent_image'
    FROM tbl_category c
    LEFT JOIN tbl_gallery_category g 
        ON c.cid=g.cat_id
    LEFT JOIN tbl_gallery n 
        ON n.id=g.image_id

    WHERE n.date_active < NOW()
    GROUP BY c.cid
    ORDER BY c.category_name ASC
) a

INNER JOIN tbl_gallery gg
    ON a.recent_image = gg.id;

一切都返回正常，除了 MAX(n.id) 忽略 WHERE 子句并返回类别的顶级 id。如何让 MAX(n.id) 返回包含 WHERE 子句的结果集的最大值？谢谢。

Answer 1

您应该在 on 子句中使用 where 条件，添加适当的 AND .. 否则，where 将用作内连接条件

SELECT  a.*,
    gg.image as 'recent_image_file' 
FROM 

(   SELECT  c.cid, 
            c.category_name AS 'cat_name',
            COUNT(g.id) AS image_count,
            c.category_image AS 'images',
            c.estgendpref,
            MAX(n.id) as 'recent_image'
    FROM tbl_category c
    LEFT JOIN tbl_gallery_category g  ON c.cid=g.cat_id
    LEFT JOIN tbl_gallery n ON n.id=g.image_id and n.date_active < NOW()
    GROUP BY c.cid
    ORDER BY c.category_name ASC
) a

INNER JOIN tbl_gallery gg
    ON a.recent_image = gg.id;