时间戳

首页 > 解决方案 > 使用 PostgreSQL 13 上的另一列使用 string_agg 订购 DISTINCT？

sql - 使用 PostgreSQL 13 上的另一列使用 string_agg 订购 DISTINCT？

问题描述

我有一张emails桌子：

CREATE TABLE public.emails (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  name  text not null
);

我有一张contacts桌子：

CREATE TABLE public.contacts (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  email_id            bigint NOT NULL,
  full_name            text NOT NULL,
  ordering  int  not null
);

并记录如下：

insert into emails (name) VALUES ('dennis1');
insert into emails (name) VALUES ('dennis2');

insert into contacts (id, email_id, full_name, ordering) VALUES (5, 1, 'dennis1', 9);
insert into contacts (id, email_id, full_name, ordering) VALUES (6, 2, 'dennis1', 5);
insert into contacts (id, email_id, full_name, ordering) VALUES (7, 2, 'dennis5', 1);
insert into contacts (id, email_id, full_name, ordering) VALUES (8, 1, 'john', 2);

我的查询获取数据如下：

SELECT
  "emails"."name",
  STRING_AGG(DISTINCT CAST("contacts"."id" AS TEXT), ','
  ORDER BY CAST("contacts"."id" AS TEXT)) AS "contact_ids"
FROM "emails"
  INNER JOIN "contacts"
    ON ("contacts"."email_id" = "emails"."id")
WHERE "emails"."id" > 0
GROUP BY "emails"."name"
ORDER BY "emails"."name" DESC LIMIT 50

实际结果

name       contact_ids
dennis2    6,7
dennis1    5,8

预期结果

name       contact_ids
dennis2    7,6
dennis1    8,5

我想contact_ids根据ordering列排序，DESC但我不想获取ordering列。只需使用它来订购联系人的 ID。

如何根据列对contact_ids每个进行排序？idordering

演示：https ://dbfiddle.uk/?rdbms=postgres_12&fiddle=4d6851ec67b579608427bb399eae5891

标签： sqlpostgresql

解决方案

如果您在加入之前聚合，则不需要 DISTINCT，您可以随意订购任何您想要的东西：

select em.name,
       c.contact_ids 
from emails em
  join (       
    select email_id, string_agg(id::text, ',' order by ordering desc) as contact_ids
    from contacts
    group by email_id
  ) c on c.email_id = em.id
order by em.name desc 
limit 50;

在线示例

推荐阅读