代码之家  ›  专栏  ›  技术社区  ›  sçuçu

SQL JOIN查询中的重复条目

data-modeling join postgresql sql
  •  1
  • sçuçu  · 技术社区  · 8 年前

    我有三张桌子 帖子 , 评论 和 用户 .posts表包含两种类型的帖子, 问题 和 答复 .对问题和答案进行评论。我的目的是得到一个带有评论的问题,对它的回答和对那个答案的评论。此外,我还需要用户表中的用户名作为我获取的每个问题、答案和评论的作者。我正在使用Postgres 9.5,并利用 json_agg() 作用

    虽然我需要的示例输出应该与下面第一个类似,但我得到了重复的条目。

    我在这里错过了什么?正确的分组条件可能是。或者,用子查询来收集答案和他们的评论不是这样做的。当我在评论表上注释掉帖子中的左连接时,我得到了期望的结果,而没有对问题进行评论。此外,当我取消包含子查询的左联接时,我会得到我所期望的非重复结果,而且这也不是我所希望的完整数据集。这些是我迄今为止为解决我的问题收集的东西。

    我需要什么: 

    [
  {
    "post_id": "10",
    "created_at": "2016-05-10T00:16:54.469Z",
    "post_type": "question",
    "post_title": "qwerty",
    "post_text": "asdasd asda sdasd",
    "post_author_id": 1,
    "author": "isikfsc",
    "parent_post_id": null,
    "is_accepted": null,
    "acceptor_id": null,
    "answers": [
      {
        "post_id": 17,
        "created_at": "2016-05-10T04:58:56.350229",
        "post_type": "answer",
        "post_title": null,
        "post_text": "222asda dasdad asdada",
        "post_author_id": 1,
        "author": "isikfsc",
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 5,
            "created_at": "2016-05-10T10:56:30.220128",
            "text": "qweqwe",
            "author_id": 1,
            "author": "isikfsc",
            "parent_post_id": 17
          },
          {
            "id": 8,
            "created_at": "2016-05-10T11:00:00.182991",
            "text": "sasasd",
            "author_id": 1,
            "author": "isikfsc",
            "parent_post_id": 17
          }
        ]
      },
      {
        "post_id": 14,
        "created_at": "2016-05-10T04:19:19.005556",
        "post_type": "answer",
        "post_title": null,
        "post_text": "asdasdasdasd",
        "post_author_id": 1,
        "author": "isikfsc",
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 2,
            "created_at": "2016-05-10T05:25:34.671008",
            "text": "qeqweqwe",
            "author_id": 1,
            "author": "isikfsc",
            "parent_post_id": 14
          }
        ]
      }
    ],
    "comments": [
        {
          "id": 1,
          "created_at": "2016-05-10T10:56:30.220128",
          "text": "qweqwe",
          "author_id": 1,
          "author": "isikfsc",
          "parent_post_id": 10
        },
        {
          "id": 4,
          "created_at": "2016-05-10T11:00:00.182991",
          "text": "sasasd",
          "author_id": 1,
          "author": "isikfsc",
          "parent_post_id": 10
        }
    ]
  }
]

    我的查询是: 

    SELECT
    q.*,
    json_agg(ac.*) AS answers,
    json_agg(c.*) AS comments --comments on posts of post_id questions
FROM posts q

LEFT JOIN 
    (
        SELECT
            a.*,
            json_agg(c.*) AS comments -- comments on posts of post_id answers
        FROM posts a
        LEFT JOIN comments c
        ON a.post_id = c.parent_post_id

        GROUP BY a.post_id
    ) ac
ON q.post_id = ac.parent_post_id

LEFT JOIN comments c
ON q.post_id = c.parent_post_id

WHERE q.post_id = 10
GROUP BY q.post_id

    我得到了什么: 

    [
  {
    "post_id": "10",
    "created_at": "2016-05-10T00:16:54.469Z",
    "post_type": "question",
    "post_title": "qwerty",
    "post_text": "asdasd asda sdasd",
    "post_author_id": 1,
    "parent_post_id": null,
    "is_accepted": null,
    "acceptor_id": null,
    "answers": [
      {
        "post_id": 17,
        "created_at": "2016-05-10T04:58:56.350229",
        "post_type": "answer",
        "post_title": null,
        "post_text": "222asda dasdad asdada",
        "post_author_id": 1,
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 5,
            "created_at": "2016-05-10T10:56:30.220128",
            "text": "qweqwe",
            "author_id": 1,
            "parent_post_id": 17
          },
          {
            "id": 8,
            "created_at": "2016-05-10T11:00:00.182991",
            "text": "sasasd",
            "author_id": 1,
            "parent_post_id": 17
          }
        ]
      },
      {
        "post_id": 17,
        "created_at": "2016-05-10T04:58:56.350229",
        "post_type": "answer",
        "post_title": null,
        "post_text": "222asda dasdad asdada",
        "post_author_id": 1,
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 5,
            "created_at": "2016-05-10T10:56:30.220128",
            "text": "qweqwe",
            "author_id": 1,
            "parent_post_id": 17
          },
          {
            "id": 8,
            "created_at": "2016-05-10T11:00:00.182991",
            "text": "sasasd",
            "author_id": 1,
            "parent_post_id": 17
          }
        ]
      },
      {
        "post_id": 17,
        "created_at": "2016-05-10T04:58:56.350229",
        "post_type": "answer",
        "post_title": null,
        "post_text": "222asda dasdad asdada",
        "post_author_id": 1,
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 5,
            "created_at": "2016-05-10T10:56:30.220128",
            "text": "qweqwe",
            "author_id": 1,
            "parent_post_id": 17
          },
          {
            "id": 8,
            "created_at": "2016-05-10T11:00:00.182991",
            "text": "sasasd",
            "author_id": 1,
            "parent_post_id": 17
          }
        ]
      },
      {
        "post_id": 17,
        "created_at": "2016-05-10T04:58:56.350229",
        "post_type": "answer",
        "post_title": null,
        "post_text": "222asda dasdad asdada",
        "post_author_id": 1,
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 5,
            "created_at": "2016-05-10T10:56:30.220128",
            "text": "qweqwe",
            "author_id": 1,
            "parent_post_id": 17
          },
          {
            "id": 8,
            "created_at": "2016-05-10T11:00:00.182991",
            "text": "sasasd",
            "author_id": 1,
            "parent_post_id": 17
          }
        ]
      },
      {
        "post_id": 14,
        "created_at": "2016-05-10T04:19:19.005556",
        "post_type": "answer",
        "post_title": null,
        "post_text": "asdasdasdasd",
        "post_author_id": 1,
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 2,
            "created_at": "2016-05-10T05:25:34.671008",
            "text": "qeqweqwe",
            "author_id": 1,
            "parent_post_id": 14
          }
        ]
      },
      {
        "post_id": 14,
        "created_at": "2016-05-10T04:19:19.005556",
        "post_type": "answer",
        "post_title": null,
        "post_text": "asdasdasdasd",
        "post_author_id": 1,
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 2,
            "created_at": "2016-05-10T05:25:34.671008",
            "text": "qeqweqwe",
            "author_id": 1,
            "parent_post_id": 14
          }
        ]
      },
      {
        "post_id": 14,
        "created_at": "2016-05-10T04:19:19.005556",
        "post_type": "answer",
        "post_title": null,
        "post_text": "asdasdasdasd",
        "post_author_id": 1,
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 2,
            "created_at": "2016-05-10T05:25:34.671008",
            "text": "qeqweqwe",
            "author_id": 1,
            "parent_post_id": 14
          }
        ]
      },
      {
        "post_id": 14,
        "created_at": "2016-05-10T04:19:19.005556",
        "post_type": "answer",
        "post_title": null,
        "post_text": "asdasdasdasd",
        "post_author_id": 1,
        "parent_post_id": 10,
        "is_accepted": null,
        "acceptor_id": null,
        "comments": [
          {
            "id": 2,
            "created_at": "2016-05-10T05:25:34.671008",
            "text": "qeqweqwe",
            "author_id": 1,
            "parent_post_id": 14
          }
        ]
      }
    ],
    "comments": [
      {
        "id": 1,
        "created_at": "2016-05-10T05:25:28.200327",
        "text": "asadasdad",
        "author_id": 1,
        "parent_post_id": 10
      },
      {
        "id": 4,
        "created_at": "2016-05-10T10:25:23.381177",
        "text": "werwer",
        "author_id": 1,
        "parent_post_id": 10
      },
      {
        "id": 1,
        "created_at": "2016-05-10T05:25:28.200327",
        "text": "asadasdad",
        "author_id": 1,
        "parent_post_id": 10
      },
      {
        "id": 4,
        "created_at": "2016-05-10T10:25:23.381177",
        "text": "werwer",
        "author_id": 1,
        "parent_post_id": 10
      },
      {
        "id": 1,
        "created_at": "2016-05-10T05:25:28.200327",
        "text": "asadasdad",
        "author_id": 1,
        "parent_post_id": 10
      },
      {
        "id": 4,
        "created_at": "2016-05-10T10:25:23.381177",
        "text": "werwer",
        "author_id": 1,
        "parent_post_id": 10
      },
      {
        "id": 1,
        "created_at": "2016-05-10T05:25:28.200327",
        "text": "asadasdad",
        "author_id": 1,
        "parent_post_id": 10
      },
      {
        "id": 4,
        "created_at": "2016-05-10T10:25:23.381177",
        "text": "werwer",
        "author_id": 1,
        "parent_post_id": 10
      }
    ]
  }
]
    1 回复  |  直到 8 年前
        1
  •  1
  •   Ezequiel Tolnay    8 年前

    一旦所有参与方都已加入,就会进行分组,因此聚合将取决于生成的基数。将带有答案和注释的帖子连接起来会导致它们之间的完全连接,从而复制所有值。它们需要分开并单独执行,一种方法是: 

    SELECT
    q.*,
    (SELECT json_agg(ac.*)
     FROM (
       SELECT a.*, json_agg(c.*) AS comments
       FROM posts a
       LEFT JOIN comments c ON (a.post_id = c.parent_post_id)
       WHERE a.parent_post_id = q.post_id
       GROUP BY a.post_id
       ) ac
    ) AS answers,
    json_agg(c.*) AS comments --comments on posts of post_id questions
FROM posts q
LEFT JOIN comments c ON (q.post_id = c.parent_post_id)
WHERE q.post_id = 10
GROUP BY q.post_id;

    或者: 

    SELECT q.*, qa.answers, qc.comments
FROM posts q
LEFT JOIN (
  SELECT ac.parent_post_id, json_agg(ac.*) AS answers
  FROM (
    SELECT ac.*, json_agg(c.*) AS comments
    FROM posts ac
    LEFT JOIN comments c ON (c.parent_post_id = ac.post_id)
    GROUP BY ac.post_id
    ) ac
  GROUP BY ac.parent_post_id
  ) qa ON (qa.parent_post_id = q.post_id)
LEFT JOIN (
  SELECT c.parent_post_id, json_agg(c.*) AS comments
  FROM comments c
  GROUP BY c.parent_post_id
  ) qc ON (qc.parent_post_id = q.post_id)
WHERE q.post_id = 10;
    推荐文章
    Ishaan Adarsh  ·  未知的META规范,无法验证。[规范v1.0.1]
    1 年前
    tggtsed  ·  PostgreSQL从平均值中排除值
    1 年前
    Dawid  ·  为什么我不能在子查询中使用表别名?
    2 年前
    LAZ  ·  ORDER BY id DESC LIMIT 100返回第一行而不是最后一行
    2 年前
    Mohammed Shaheer  ·  查找列中的数据是否满足sql中的数据类型条件
    2 年前
    AKang123.  ·  Postgres:重复行,但更新json中的字段
    2 年前
    CraZyCoDer  ·  在PostgreSQL中锁定潜在事务
    2 年前
    ranebec  ·  计数时如何返回0而不是null?
    2 年前
    Ramón J Romero y Vigil  ·  将没有时区列的postgres时间戳转换为日期
    2 年前
    Flo  ·  分组依据中的SQL大小写
    2 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号