PostgreSQL：如何对string_agg聚合字段使用LIKE过滤结果

你遇到的问题是SQL执行顺序导致的：WHERE子句的执行早于SELECT阶段的字段别名生成，当WHERE执行时，Roles这个聚合后的别名还不存在，所以会报错。

有两种常用的解决方法：

方法1：使用HAVING子句过滤聚合结果 #

HAVING子句在GROUP BY聚合之后执行，PostgreSQL支持直接引用SELECT里定义的聚合别名，修改后的SQL如下：

SELECT 
  Names.UserId, 
  Names.name, 
  string_agg(UserRoles.Rolename, ', ') as Roles
FROM 
  Names, UserRoles
WHERE 
  names.UserId = UserRoles.UserId
GROUP BY 
  Names.UserId, Names.name
HAVING 
  Roles LIKE '%Ma%'
ORDER BY 
  Names.UserId;

如果要兼容更多数据库，也可以直接使用聚合函数本身：

HAVING 
  string_agg(UserRoles.Rolename, ', ') LIKE '%Ma%'

方法2：用子查询/CTE先聚合再过滤 #

如果需要更复杂的过滤逻辑，或者想让代码逻辑更清晰，可以先通过子查询或CTE生成包含聚合字段的结果集，再在外层WHERE中过滤：

子查询写法 #

SELECT *
FROM (
  SELECT 
    Names.UserId, 
    Names.name, 
    string_agg(UserRoles.Rolename, ', ') as Roles
  FROM 
    Names, UserRoles
  WHERE 
    names.UserId = UserRoles.UserId
  GROUP BY 
    Names.UserId, Names.name
) AS aggregated_data
WHERE 
  Roles LIKE '%Ma%'
ORDER BY 
  UserId;

CTE写法（可读性更强） #

WITH aggregated_data AS (
  SELECT 
    Names.UserId, 
    Names.name, 
    string_agg(UserRoles.Rolename, ', ') as Roles
  FROM 
    Names, UserRoles
  WHERE 
    names.UserId = UserRoles.UserId
  GROUP BY 
    Names.UserId, Names.name
)
SELECT *
FROM aggregated_data
WHERE Roles LIKE '%Ma%'
ORDER BY UserId;

额外建议 #

建议把隐式连接（FROM Names, UserRoles）改成显式INNER JOIN写法，代码可读性更高：

SELECT 
  Names.UserId, 
  Names.name, 
  string_agg(UserRoles.Rolename, ', ') as Roles
FROM 
  Names
INNER JOIN UserRoles ON Names.UserId = UserRoles.UserId
GROUP BY 
  Names.UserId, Names.name
HAVING 
  Roles LIKE '%Ma%'
ORDER BY 
  Names.UserId;

配套建表与测试数据 #

CREATE TABLE UserRoles
    (UserId int, Rolename varchar(15))
;

CREATE TABLE Names
    (UserId int, Name varchar(15))
;

INSERT INTO UserRoles
    (UserId, Rolename)
VALUES
    (1, 'Manager'),
    (1, 'Event Organiser'),
    (2, 'Supervisor'),
    (2, 'Employee'),
    (2, 'Some otherRole')
;

INSERT INTO Names
    (UserId, Name)
VALUES
    (1, 'Jacob'),
    (2, 'Mary');

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