sql - 使用 PostgreSQL 删除行重复并获得最高值

Question

使用以下查询

SELECT
  users.id,
  users.username,
  users.email,
  groups.permission
FROM users
INNER JOIN memberships ON users.id = memberships.user_id
INNER JOIN groups ON memberships.group_id = groups.id
INNER JOIN groupings ON groups.id = groupings.group_id
WHERE groupings.repo_id = 1
GROUP BY users.id, groups.permission
ORDER BY users.id

我得到以下行:

----+---------------------------+-----------------------------------------+------------
 id |         username          |                  email                  | permission 
----+---------------------------+-----------------------------------------+------------
  2 | viva_leuschke0            | viva_leuschke0@ritchiealtenwerth.org    | 1
  2 | viva_leuschke0            | viva_leuschke0@ritchiealtenwerth.org    | 2
  3 | loyce_herman1             | herman1.loyce@bednar.com                | 1
  3 | loyce_herman1             | herman1.loyce@bednar.com                | 3
  4 | verona_vandervort2        | verona.vandervort2@mante.biz            | 1
  4 | verona_vandervort2        | verona.vandervort2@mante.biz            | 2
  4 | verona_vandervort2        | verona.vandervort2@mante.biz            | 3
  5 | bruen3_ms_hans            | ms.bruen3.hans@bechtelar.net            | 1
  5 | bruen3_ms_hans            | ms.bruen3.hans@bechtelar.net            | 2
  5 | bruen3_ms_hans            | ms.bruen3.hans@bechtelar.net            | 3
----+---------------------------+-----------------------------------------+------------

问题是:如何调整此查询以返回唯一行，按最高权限值过滤？像这样的东西:

----+---------------------------+-----------------------------------------+------------
 id |         username          |                  email                  | permission 
----+---------------------------+-----------------------------------------+------------
  2 | viva_leuschke0            | viva_leuschke0@ritchiealtenwerth.org    | 2
  3 | loyce_herman1             | herman1.loyce@bednar.com                | 3
  4 | verona_vandervort2        | verona.vandervort2@mante.biz            | 3
  5 | bruen3_ms_hans            | ms.bruen3.hans@bechtelar.net            | 3
----+---------------------------+-----------------------------------------+------------

我正在使用 PostgreSQL 9.1。

更新:通过使用 DISTINCT ON 子句，我能够得到我想要的。

SELECT DISTINCT ON(users.id)
  users.id,
  users.username,
  users.email,
  groups.permission
FROM users
INNER JOIN memberships ON users.id = memberships.user_id
INNER JOIN groups ON memberships.group_id = groups.id
INNER JOIN groupings ON groups.id = groupings.group_id
WHERE groupings.repo_id = 1
GROUP BY users.id, groups.permission
ORDER BY 
  users.id ASC, 
  groups.permission DESC

这是最好的方法吗？

Answer 1

经过仔细检查，您的查询可以(并且应该)得到改进:

SELECT DISTINCT ON (u.id)
      ,u.id
      ,u.username
      ,u.email
      ,g.permission
FROM   users       u
JOIN   memberships m  ON m.user_id = u.id
JOIN   groups      g  ON g.id = m.group_id
JOIN   groupings   gi ON gi.group_id = g.id 
WHERE  gi.repo_id = 1
<strike>GROUP  BY u.id, g.permission</strike>
ORDER  BY u.id, g.permission DESC

GROUP BY 没有用。 DISTINCT ON 单独给你一个唯一的 users.id。删除 GROUP BY，您将获得相同的结果，只是速度更快。

您可能希望向 ORDER BY 添加更多列，以决定选择哪一行，其中每个 id 有多个列共享最高的 权限 .按照您的方式，您可以任意选择这种情况(前提是它可能发生)。

My reference answer for this query technique has more explanation, a benchmark and links.