我有一个appointment表,其中的两个字段petsservices是数组。我正在尝试创建一个查询,其中列出了starts_atends_at时间以及与该约会相关的服务和宠物的名称。
我遇到的问题是,如果petsservices包含的值超过1个,则在输出中会遇到重复的值。
以下是我目前的查询:

SELECT
    appointment.starts_at,
    appointment.ends_at,
    string_agg(service.name, ', ') AS service_names,
    string_agg(pet.name, ', ') AS pet_names
FROM get_appointments(20, CURRENT_DATE, CAST((CURRENT_DATE + INTERVAL '1 day' * 4) AS DATE), null, null) AS appointment
INNER JOIN pet on pet.id = ANY(appointment.pets)
INNER JOIN service on service.id = ANY(appointment.services)
GROUP BY
    appointment.starts_at,
    appointment.ends_at
ORDER BY
    appointment.starts_at ASC;

下面是示例输出:
+---------------------+---------------------+--------------------------------+-------------------+
|      starts_at      |       ends_at       |         service_names          |     pet_names     |
+---------------------+---------------------+--------------------------------+-------------------+
| 2017-05-03 07:00:00 | 2017-05-03 07:30:00 | 30 Minute Walk, 30 Minute Walk | Gregor, The Hound |
+---------------------+---------------------+--------------------------------+-------------------+

以下是所需的输出:
+---------------------+---------------------+--------------------------------+-------------------+
|      starts_at      |       ends_at       |         service_names          |     pet_names     |
+---------------------+---------------------+--------------------------------+-------------------+
| 2017-05-03 07:00:00 | 2017-05-03 07:30:00 | 30 Minute Walk          | Gregor, The Hound |
+---------------------+---------------------+--------------------------------+-------------------+

我在读9.5级的博士后。
谢谢您!

最佳答案

子pp
同样使用'aa:
子pp

关于sql - 在数组列上使用内部联接时重复的值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43771161/

10-12 00:42
查看更多