FILTER子句不能使用聚合函数的SQL查询报错修复咨询

错误原因 #

你遇到的第一个错误是因为FILTER的WHERE子句不能包含聚合函数（比如MAX(messages.created_at)）。FILTER是在分组聚合前对行进行筛选，此时聚合结果还未计算生成，无法用它来作为筛选条件。

改成HAVING也不对，因为HAVING是用于分组完成后对整个分组进行过滤的关键字，不能放在FILTER语法内部，所以会触发语法错误。

修复方案：两种可行写法 #

方法一：使用窗口函数标记最新消息（推荐）

通过窗口函数ROW_NUMBER()给每个联系人的消息按时间倒序排名，取排名第一的即为最新消息，再结合聚合计算未读数量：

WITH ranked_messages AS (
    SELECT
        m.*,
        -- 按联系人（对方ID）分组，消息时间倒序排名，最新消息排第1
        ROW_NUMBER() OVER (
            PARTITION BY CASE WHEN m.from_id = 14 THEN m.to_id ELSE m.from_id END 
            ORDER BY m.created_at DESC
        ) AS rn
    FROM messages m
    WHERE m.from_id = 14 OR m.to_id = 14
)
SELECT
    u.id,
    u.name,
    u.avatar,
    rm.created_at AS max_created_at,
    rm.body AS last_message,
    -- 判断是否所有消息已读
    CASE WHEN COUNT(m.is_read) FILTER (WHERE m.is_read = false AND m.from_id != 14) = 0 
         THEN true ELSE false END AS is_read,
    -- 统计未读消息数量
    COUNT(m.is_read) FILTER (WHERE m.is_read = false AND m.from_id != 14) AS count_unread
FROM ranked_messages rm
JOIN users u ON 
    (rm.from_id = u.id AND u.id != 14) OR (rm.to_id = u.id AND u.id != 14)
-- 关联所有消息用于计算未读数量
LEFT JOIN messages m ON 
    ((m.from_id = 14 AND m.to_id = u.id) OR (m.to_id = 14 AND m.from_id = u.id))
WHERE rm.rn = 1 -- 只保留每个联系人的最新消息
GROUP BY u.id, u.name, u.avatar, rm.created_at, rm.body;

方法二：先获取最新消息时间，再关联取内容

先通过子查询算出每个联系人的最新消息时间，再关联消息表获取对应内容，最后聚合未读数量：

SELECT
    u.id,
    u.name,
    u.avatar,
    latest.max_created_at,
    m_last.body AS last_message,
    CASE WHEN COUNT(m.is_read) FILTER (WHERE m.is_read = false AND m.from_id != 14) = 0 
         THEN true ELSE false END AS is_read,
    COUNT(m.is_read) FILTER (WHERE m.is_read = false AND m.from_id != 14) AS count_unread
FROM (
    -- 子查询：获取每个联系人的最新消息时间
    SELECT
        CASE WHEN from_id = 14 THEN to_id ELSE from_id END AS contact_id,
        MAX(created_at) AS max_created_at
    FROM messages
    WHERE from_id = 14 OR to_id = 14
    GROUP BY contact_id
) latest
JOIN users u ON latest.contact_id = u.id
-- 关联最新消息的具体内容
JOIN messages m_last ON 
    ((m_last.from_id = 14 AND m_last.to_id = u.id) OR (m_last.to_id = 14 AND m_last.from_id = u.id))
    AND m_last.created_at = latest.max_created_at
-- 关联所有消息统计未读数量
LEFT JOIN messages m ON 
    ((m.from_id = 14 AND m.to_id = u.id) OR (m.to_id = 14 AND m.from_id = u.id))
GROUP BY u.id, u.name, u.avatar, latest.max_created_at, m_last.body;

额外优化提示 #

原查询中的COUNT(messages.is_read)可以简化为COUNT(*)，因为FILTER已经筛选出is_read = false的行，两种写法结果一致，可读性更好。

内容的提问来源于stack exchange，提问作者BoboBoy