sql - 使用 'group by' 和 'join' 时单个查询中的多个选择语句

Question

我有三张 table -podcasts , videos和 others .
这些下的每个实体都与一个 category 相关联。和 subcategories .
播客示例 -

这个 category_id 和 subcategory_id 在它们各自的表中有它们的名称值 -

现在，我想获取每个类别和子类别组合下的播客、视频和文本的数量。我的个人 SQL 查询是这些 -
对于 podcasts ——

SELECT c.category_name, sc.sub_category_name, count(p.*) AS podcast_count
FROM podcasts p
JOIN categories c ON c.category_id = p.podcast_category_id
JOIN sub_categories sc ON sc.sub_category_id = p.podcast_subcategory_id
WHERE p.podcast_owner = 14 AND p.podcast_upload_time_stamp >= timestamp '2020-10-22 00:00:00'
GROUP BY 1, 2

对于 others ——

SELECT c.category_name, sc.sub_category_name, count(o.*) AS other_count
FROM otherlinks o
JOIN categories c ON c.category_id = o.other_link_category_id
JOIN sub_categories sc ON sc.sub_category_id = o.other_link_subcategory_id
WHERE o.other_link_owner = 14 AND o.other_link_add_time_stamp >= timestamp '2020-10-22 00:00:00'
GROUP BY 1, 2

和类似的一个 videos现在，我想将它们组合成一个查询，以便在单个结果中获得三列计数 - podcast_count , other_count和 videos_count .我怎么做？

Answer 1

加入CROSS加盟categories至 sub_categories ，因此您可以使用 LEFT 将类别和子类别的所有组合添加到其他 3 个表中按每个组合和聚合加入和分组:

select c.category_name, sc.sub_category_name,
       count(distinct p.podcast_id) podcast_count,
       count(distinct v.video_id) videos_count,  
       count(distinct o.other_link_id) other_count 
from categories c cross join sub_categories sc
left join podcasts p on (p.podcast_category_id, p.podcast_subcategory_id) = (c.category_id, sc.sub_category_id)
  and p.podcast_owner = 14 AND p.podcast_upload_time_stamp >= timestamp '2020-10-22 00:00:00'
left join videos v on (v.video_category_id, v.video_subcategory_id) = (c.category_id, sc.sub_category_id)
  and v.video_owner = 14 AND v.video_upload_time_stamp >= timestamp '2020-10-22 00:00:00'
left join otherlinks o on (o.other_link_category_id, o.other_link_subcategory_id) = (c.category_id, sc.sub_category_id)
  and o.other_link_owner = 14 AND o.other_link_add_time_stamp >= timestamp '2020-10-22 00:00:00'
where coalesce(p.podcast_id, v.video_id, o.other_link_id) is not null
group by c.category_id, c.category_name, sc.sub_category_id, sc.sub_category_name

WHERE 子句过滤掉不包含任何播客、视频或其他链接的类别和子类别的任何组合。