sql - 使用“分组依据”和“加入”时，单个查询中的多个选择语句

Question

我有三张桌子 -

podcasts,videos和others.

这些下的每个实体都与 acategory和相关联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_countvideos_count

标签： sqlpostgresql

Answer 1

加入to的CROSS连接，因此您可以获得类别和子类别的所有组合，通过连接和按每个组合分组并聚合的其他 3 个表：categoriessub_categoriesLEFT

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 子句过滤掉不包含任何播客、视频或其他链接的类别和子类别的任何组合。