时间戳

首页 > 解决方案 > 如何使用 postgresql 中的 group by 子句获取关联表列的值？

sql - 如何使用 postgresql 中的 group by 子句获取关联表列的值？

问题描述

有 3 个表，即 items、fields 和 field_values，如下所示。我根据两个字段 item.name 和 fields.name 对记录进行分组。我希望 sql 查询返回 items.name、sum(items.qty) 和 field_values.value 数组（与分组记录相关联）。

表项

ID 姓名 数量
1 面条 1
2 饼干 1
3 文件 1

表字段

ID 姓名
1 分行代码
2 分公司经理姓名

表 field_values

ID 价值 item_id field_id
1 分公司1 1 1
2 分公司2 2 1
3 办事处分部 3 1 1
4 办事处 4 1 1
5 办事处分部 5 3 1

预期：根据表 field_values 中的数据

总数量为 3 的项目面条与办公室分店 1、办公室分店 3、办公室分店 4 链接

数量 1 的项目饼干与分公司 2 链接

数量为 1 的项目文件与办公室分支 5 链接

预期结果

姓名 数量 Delivery_to_branches
面条 3 {"办公室分支 1","办公室分支 3","办公室分支 4"}
饼干 1 {"分公司 2"}
文件 1 {"办公室分部 5"}

我正在运行以下 sql 查询：

SELECT
    items.name,
    sum(qty) AS quantity,
    array(
        SELECT field_values.value
            FROM field_values
        WHERE field_values.item_id IN (
            SELECT ID FROM items
        )
        AND fields.name = 'branch code'
    ) AS "delivery_to_branches"
FROM
  items
INNER JOIN field_values ON field_values.item_id = items.id
INNER JOIN fields ON field_values.field_id = fields.id
GROUP BY
    items.name, fields.name

字段 delivery_to_branches 得到意外结果

当我执行上面的 Sql 查询时，我得到了正确的名称和数量值，但数组值显示为 delivery_to_branches 不正确。任何帮助将不胜感激，谢谢！

姓名 数量 Delivery_to_branches
面条 3 {"办公室分支 1", "办公室分支 2","办公室分支 3","办公室分支 4", "办公室分支 5"}
饼干 1 {"办公室分支 1", "办公室分支 2","办公室分支 3","办公室分支 4", "办公室分支 5"}
文件 1 {"办公室分支 1", "办公室分支 2","办公室分支 3","办公室分支 4", "办公室分支 5"}

标签： sqlpostgresql

解决方案

我认为您只想array_agg()在带有过滤器的外部查询中：

SELECT i.name,
       SUM(qty) AS quantity,
       ARRAY_AGG(fv.value) FILTER (WHERE f.name = 'branch code') as delivery_to_branches
FROM items i JOIN
     field_values fv
     ON fv.item_id = i.id JOIN
     fields f
     ON fv.field_id = f.id

按 i.name 分组；

推荐阅读