如何为SQL查询中动态生成的别名字段应用HAVING子句？

你的原查询尝试用HAVING子句筛选动态生成的hasReceivedMessageFromAgent字段为true的记录，但HAVING通常配合GROUP BY用于分组后的筛选，无分组时使用不符合SQL规范，且部分数据库可能报错。以下是两种更规范的解决方法：

方法1：将过滤逻辑移到WHERE子句（推荐） #

直接把EXISTS子查询放到WHERE中，既实现筛选，又避免生成多余字段，执行效率更高：

SELECT 
  v_list_client.id AS "id",
  true AS "hasReceivedMessageFromAgent",
  *
FROM v_list_client
LEFT JOIN v_form
  ON (v_form."client" ->> 'id')::integer = v_list_client.id
WHERE 
  (v_form."formTypeId")::integer = 1
  AND (v_form."isReceived")::boolean = true
  AND EXISTS(
    SELECT id FROM message 
    WHERE to_phone_number = v_list_client."phoneNumber" 
      AND created_by_agent_id != (
        SELECT id FROM agent 
        WHERE agent_role_id = (
          SELECT id FROM agent_role WHERE label = 'Bot'
        )
      )
  )

方法2：用子查询包裹后在外层筛选 #

如果需要保留SELECT中的hasReceivedMessageFromAgent字段，可以将原查询作为子查询，在外层通过WHERE筛选：

SELECT *
FROM (
  SELECT 
    v_list_client.id AS "id",
    EXISTS(
      SELECT id FROM message 
      WHERE to_phone_number = v_list_client."phoneNumber" 
        AND created_by_agent_id != (
          SELECT id FROM agent 
          WHERE agent_role_id = (
            SELECT id FROM agent_role WHERE label = 'Bot'
          )
        )
    ) AS "hasReceivedMessageFromAgent",
    *
  FROM v_list_client
  LEFT JOIN v_form
    ON (v_form."client" ->> 'id')::integer = v_list_client.id
  WHERE 
    (v_form."formTypeId")::integer = 1
    AND (v_form."isReceived")::boolean = true
) AS subquery
WHERE "hasReceivedMessageFromAgent" = true

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