sql - 使用 Postgres 聚合函数选择每个 GROUP BY 组中的第一行？

Question

我有两个表:user 用于跟踪用户信息，users_classes 用于跟踪用户和类之间的关系(类在单独的表中)。我想加入表并返回用户的信息以及他们所在的类的数组。有没有一种有效的方法可以做到这一点？

表结构:

CREATE TABLE users (
  id        BIGSERIAL PRIMARY KEY,
  username  VARCHAR(255) UNIQUE,
  email     VARCHAR(255) NOT NULL UNIQUE
)

CREATE TABLE users_classes (
  user_id   BIGINT REFERENCES users(id),
  class_id  BIGINT REFERENCES classes(id),
  PRIMARY KEY(user_id, class_id)
)

我的尝试:

SELECT
  u.id,
  FIRST(u.username) AS username,
  FIRST(u.email) AS email,
  array_agg(c.class_id) AS classes
FROM users_classes AS c
JOIN users AS u
ON c.user_id = u.id
GROUP BY u.id;

我认为这可行(在实现 FIRST() 之后)，但是否有更标准的方法来做到这一点？

Answer 1

只需将列添加到 GROUP BY 子句中，如下所示:

SELECT
  u.id,
  u.username,
  u.email,
  array_agg(c.class_id) AS classes
FROM users_classes AS c
  JOIN users AS u
    ON c.user_id = u.id
GROUP BY u.id, u.username, u.email;