如何查询关联多个folder类型分类的PostgreSQL资产？

需求说明 #

需要编写PostgreSQL查询语句，找出所有关联了多个type为'folder'类型分类的资产，已知资产3（Super Asset）和资产5（Hilarious Asset）符合该条件。

解决方案 #

以下是两种高效的查询方案：

方案一：子查询分组后关联资产表

SELECT a.id, a.name
FROM asset a
JOIN (
    SELECT ac.asset_id
    FROM asset_category ac
    JOIN category c ON ac.category_id = c.id
    WHERE c.type = 'folder'
    GROUP BY ac.asset_id
    HAVING COUNT(c.id) > 1
) AS folder_assets ON a.id = folder_assets.asset_id;

方案二：直接关联三张表后分组筛选

SELECT a.id, a.name, COUNT(c.id) AS folder_category_count
FROM asset a
JOIN asset_category ac ON a.id = ac.asset_id
JOIN category c ON ac.category_id = c.id
WHERE c.type = 'folder'
GROUP BY a.id, a.name
HAVING COUNT(c.id) > 1;

思路解释 #

1. 关联筛选：通过asset_category中间表关联资产表与分类表，先过滤出所有属于folder类型的分类关联记录。
2. 分组统计：按资产ID分组，统计每个资产关联的folder类型分类数量。
3. 结果过滤：仅保留统计数量大于1的资产，即为符合条件的目标资产。

测试验证 #

执行上述任意查询语句，返回结果如下：

完全匹配预期的目标资产。

内容的提问来源于stack exchange，提问作者bln_dev