时间戳

首页 > 解决方案 > 为选定的父母组中的每个成员选择一组多个孩子（也包括他们自己的父母）

sql - 为选定的父母组中的每个成员选择一组多个孩子（也包括他们自己的父母）

问题描述

我使用 PostgreSQL 构建了以下方案-

Table A:
    id | name
    1  | a
    2  | b 
Table B:
    id | parent-A-id  | searchValue | dataB
    1  | 1            | val1        | a
    2  | 2            | val2        | b
    3  | 2            | val2        | c
Table C:
    id | parent-A-id  | dataC
    1  | 1            | x
    2  | 2            | y
    3  | 2            | z
Table D:
    id | parent-A-id  | parent-B-id | searchValue-REF 
    1  | 1            | 1           | val1
    2  | 2            | 2           | val2
    2  | 3            | 2           | val2
    2  | 3            | 3           | val2

我正在尝试检索以下数据 -

给定 N，找到 n 个数量的 B、其父 A 和子 C 的子集，仅使用符合在 searchByVal-REF 上运行的函数的项 D，这意味着根据 N 值计算计算结果（例如 - 位置） .

基于 N 的数据输出示例是 -

[
    {
        A.name : "a",
        A.id : 1
        B.id : 1,
        B.dataB : "a",
        [{C.id : 1, C.dataC : "x"}],
        computedVal : someVal
    },
    {
        A.name : "b",
        A.id : 2
        B.id : 2,
        B.dataB : "b",
        [{C.id : 2, C.dataC : "y"}],
        computedVal : someVal
    },
     {
        A.name : "b",
        A.id : 2
        B.id : 3,
        B.dataB : "c",
        [{C.id : 2, C.dataC : "y"}, {C.id : 3, C.dataC : "z"}],
        computedVal : someVal
    }
]

数据基本上是每个 B 的 B 行和每个 B 的父 A 的行和每个 B 的 n 个子 C 的行序列化数组，该行表示为每行 B 的单个列。

我不确定我是否过于复杂，但目前我正试图通过将一组结果转换为序列化 json 来将一组记录 C 分组到一个列中。但是我还没有设法让它工作，因为我试图在外部查询上引用子查询，因为我认为查询过于嵌套而失败。

这就是我被困的地方 -

SELECT
  b_items_found.*,
  row_to_json(C_json_arr) as C_list
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          array_agg("parent-C-id") as selected_Cs,
          "parent-B-id",
          computedVal
        FROM
          (
            SELECT
              ComputeA(searchValue-REF) AS computedVal,
              "parent-B-id",
              "parent-C-id"
            FROM
              D
              CROSS JOIN (
                SELECT
                  ComputeC(N)
              ) AS r
            WHERE
              ComputeB(searchValue-REF)
            ORDER BY
              ComputeA(searchValue-REF)
          ) select-b-items
        GROUP BY
          "parent-B-id",
          computedVal
      ) b_and_c
      JOIN B ON b_and_c."parent-B-id" = B.id
      JOIN A ON B."parent-a-id" = A.id
  ) b_items_found,
  (
    SELECT
      array_agg(row_to_json(t)) as m
    from
      (
        SELECT
          *
        from
          C
        WHERE C.id = ANY(b_items_found.selected_Cs)
      ) t
  ) C_json_arr

这目前导致 -

表“b-items-found”有一个条目，但不能从查询的这一部分引用。

我想知道是否有一个单一的查询解决方案，或者它是否应该分成几个查询（查询和处理服务器端）。

运行 PostgreSQL 9.6.8

标签： sqljsonpostgresqlrecursive-query

解决方案

尽管查询确实看起来过于复杂，但您的主要问题是您正在尝试访问b_items_found查询中的临时表，其中优化器可能决定首先执行以下查询，这就是您无法引用它的原因（没有兄弟引用，只有子引用）。

为此，您必须使用 CTE（公用表表达式），如下所示：

WITH b_items_found AS (
    SELECT
      *
    FROM
      (
        SELECT
          array_agg("parent-C-id") as selected_Cs,
          "parent-B-id",
          computedVal
        FROM
          (
            SELECT
              ComputeA(searchValue-REF) AS computedVal,
              "parent-B-id",
              "parent-C-id"
            FROM
              D
              CROSS JOIN (
                SELECT
                  ComputeC(N)
              ) AS r
            WHERE
              ComputeB(searchValue-REF)
            ORDER BY
              ComputeA(searchValue-REF)
          ) select-b-items
        GROUP BY
          "parent-B-id",
          computedVal
      ) b_and_c
      JOIN B ON b_and_c."parent-B-id" = B.id
      JOIN A ON B."parent-a-id" = A.id
  ),
  C_json_arr AS (
    SELECT
      array_agg(row_to_json(t)) as m
    from
      (
        SELECT
          *
        from
          C
        WHERE C.id = ANY(b_items_found.selected_Cs)
      ) t
  )
SELECT
  b_items_found.*,
  row_to_json(C_json_arr) as C_list
FROM b_items_found, C_json_arr

但是，这没有经过测试，因为您的查询与您的表结构不匹配。

推荐阅读