示例:我有一些articles,comments并且我想要得到这样的东西:
articles
comments
[{ title: "Article 1", content: "Super long article goes here", comments: [ { author: "Troll", message: "You suck, Sir!" }, { author: "SpamBot", message: "http://superawesomething.com/"} ] },{ title: "Article 2", content: "Another long article goes here", comments: [ ... ] }]
现在,我看到两个解决方案:
IN
articles.content
我希望我的SQL文盲让我错过了简单的解决方案。
您可以使用聚合和/或子查询来执行此操作。就像是:
select title, content, json_agg(comments.author, comments.message) as comments from articles join comments on articles.article_id = comments.article_id group by article_id;
如果您需要将其汇总到一个字符串/ json /某物中,则将其包装到另一个汇总查询中,如下所示:
select json_agg(sub) from ( select title, content, json_agg(comments.author, comments.message) as comments from articles join comments on articles.article_id = comments.article_id group by article_id) sub;
这是一个Postgres查询。对Mysql没有任何经验。