POST表多值tag_id字段关联TAG表，统计标签组合对应帖子数

云原生阿岚

2026-8-13

问题分析

原SQL的核心问题是直接用字符串格式的tag_id与TAG表的id做等值匹配，而POST表的tag_id是分号分隔的多ID字符串，无法和单个标签ID匹配，同时也没有实现将同一帖子的多个标签合并为组合的逻辑，所以得不到预期结果。

解决方案

需要先将每个帖子的tag_id拆分为单个标签ID，关联TAG表获取标签名称，再合并为标签组合，最后统计每个组合的帖子数量。以下是主流数据库的实现：

MySQL 8.0+ 版本

SELECT tag_combination, COUNT(*) AS cnt
FROM (
    SELECT p.id, GROUP_CONCAT(t.name ORDER BY t.id SEPARATOR ', ') AS tag_combination
    FROM POST p
    -- 拆分tag_id为单个ID，处理分号和空格
    JOIN TAG t ON FIND_IN_SET(TRIM(t.id), REPLACE(p.tag_id, ';', ',')) > 0
    GROUP BY p.id
) AS post_tags
GROUP BY tag_combination
ORDER BY cnt DESC;

SQL Server 版本

SELECT tag_combination, COUNT(*) AS cnt
FROM (
    SELECT p.id, STRING_AGG(t.name, ', ') WITHIN GROUP (ORDER BY t.id) AS tag_combination
    FROM POST p
    -- 拆分分号分隔的tag_id
    CROSS APPLY STRING_SPLIT(p.tag_id, ';') AS split_tags
    JOIN TAG t ON TRIM(split_tags.value) = CAST(t.id AS VARCHAR)
    GROUP BY p.id
) AS post_tags
GROUP BY tag_combination
ORDER BY cnt DESC;

PostgreSQL 版本

SELECT tag_combination, COUNT(*) AS cnt
FROM (
    SELECT p.id, STRING_AGG(t.name, ', ' ORDER BY t.id) AS tag_combination
    FROM POST p
    -- 拆分tag_id并转换为整数数组匹配
    JOIN TAG t ON t.id = ANY(string_to_array(REPLACE(p.tag_id, ' ', ''), ';')::INT[])
    GROUP BY p.id
) AS post_tags
GROUP BY tag_combination
ORDER BY cnt DESC;

逻辑说明