关联依赖表实现各分类下文章数量统计的SQL查询需求

我有POST、CATEGORY、TAG和MIGTATION_TAG四张表，其中MIGTATION_TAG记录标签在分类间的迁移历史：比如ID为1的标签原本属于ID为10的分类，当把它的分类改成12时，MIGTATION_TAG会新增一条记录：ID=1、TAG_ID=1、CATEGORY_ID=12。

需要统计每个分类下的文章数量，规则是：

• 若标签没有迁移记录，使用TAG表的fist_category_id（注：应为first_category_id，疑似笔误）
• 若标签有迁移记录，取MIGTATION_TAG中该标签最大ID对应的分类（最新迁移记录）

表结构 #

POST表 #

id         title       content     tag_id
----------  ----------  ----------  ----------
1          title1      Text...     1
2          title2      Text...     3
3          title3      Text...     1
4          title4      Text...     2
5          title5      Text...     5
6          title6      Text...     4

CATEGORY表 #

id         name      
----------  ----------  
1          category_1      
2          category_2
3          category_3        

TAG表 #

id         name        fist_category_id
----------  ----------  ----------------
1          tag_1       1
2          tag_2       1
3          tag_3       3
4          tag_4       1
5          tag_5       2

MIGTATION_TAG表 #

id         tag_id      category_id
----------  ----------  ----------------
9          1           3
8          5           1
7          1           2
5          3           1
4          2           2
3          5           3
2          3           3
1          1           3

我的尝试（存在问题） #

我尝试用LEFT JOIN关联POST和TAG，但关联MIGTATION_TAG时无法正确获取标签的最新分类，当前SQL如下：

select category, COUNT(*) AS numer_of_posts
from(
    select CATEGORY.name,               
        case
        when POST.tag_id is not null then CATEGORY.name
        end as category              
    from POST
    left join TAG ON POST.tag_id = TAG.id
    left join  (
        select id, MAX(tag_id) tag_id 
        from MIGTATION_TAG 
        group by id, tag_id
    ) MIGTATION_TAG 
    ON TAG.id = MIGTATION_TAG.tag_id
    left join CATEGORY on MIGTATION_TAG.category_id = CATEGORY.id         
)
GROUP BY category;

期望结果 #

注意：ID为6的文章对应tag_id=4，该标签无迁移记录，需使用TAG表的fist_category_id

category   numer_of_posts     
----------  --------------  
category_1  3    
category_2  1
category_3  2      

解决方案 #

正确SQL语句 #

SELECT 
    c.name AS category,
    COUNT(p.id) AS numer_of_posts
FROM POST p
JOIN TAG t ON p.tag_id = t.id
-- 获取每个标签的最新迁移记录
LEFT JOIN (
    SELECT 
        tag_id,
        category_id,
        ROW_NUMBER() OVER (PARTITION BY tag_id ORDER BY id DESC) AS rn
    FROM MIGTATION_TAG
) mt ON t.id = mt.tag_id AND mt.rn = 1
-- 确定标签最终所属分类：有最新迁移则用迁移的分类，否则用初始分类
JOIN CATEGORY c ON COALESCE(mt.category_id, t.fist_category_id) = c.id
GROUP BY c.name
ORDER BY c.name;

关键说明 #

1. 获取最新迁移记录：用ROW_NUMBER()窗口函数按tag_id分组，按id倒序排序，取每组第一条（rn=1），即为该标签的最新迁移分类。
2. 确定最终分类：用COALESCE()函数优先取迁移记录的分类，若没有则取TAG表的初始分类。
3. 关联统计：将文章、标签、最终分类关联后，按分类名称分组统计文章数量。

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