时间戳

首页 > 解决方案 > 子查询 GROUP BY 运算符

sql - 子查询 GROUP BY 运算符

问题描述

我有一张卡片和帖子表，每个卡片和帖子条目至少有 2 个标签。当前查询使用card_to_tags_link和post_to_tags_link表来获取按特定卡片分组的所有帖子。

SELECT 
  card,
  count(post.id) AS count_1, 
  max(post.date) AS max_1

FROM 
  card 
  JOIN card_to_tags_link ON card.id = card_to_tags_link.card_id 
  JOIN post_to_tags_link ON post_to_tags_link.post_tags @>card_to_tags_link.card_tags 
  JOIN post ON post_to_tags_link.post_id = post.id 

GROUP BY 
  card.id

现在，因为每个帖子都有一post_owner列，所以我想author_url按多个帖子聚合所有帖子并按顺序排列：

SELECT ARRAY(post.author_url
from post
group_by post.author_url
order by count(post.id)
limit by 10)

这是一个单独执行的简单查询，但我想将其作为子查询添加到现有查询中：

SELECT 
  card,
  count(post.id) AS count_1, 
  max(post.date) AS max_1,
  **ADD SUBQUERY HERE**

FROM 
  card 
  JOIN card_to_tags_link ON card.id = card_to_tags_link.card_id 
  JOIN post_to_tags_link ON post_to_tags_link.post_tags @>card_to_tags_link.card_tags 
  JOIN post ON post_to_tags_link.post_id = post.id 

GROUP BY 
  card.id

是否可以将作者查询作为子查询并将结果作为数组返回？

标签： sqlpostgresql

解决方案

你只是想要ARRAY_AGG()吗？

SELECT card,
       count(post.id) AS count_1, 
        max(post.date) AS max_1,
       ARRAY_AGG(DISTINCT post.author_url)

推荐阅读