如何通过间接多对多关联查找人员与标签组的匹配关系

针对人员-标签、标签组-标签的多对多关联场景，以下是三个查询需求的无数组优化实现方案，避免数组操作带来的性能损耗：

1. 找出所有包含标签组全部标签的人员与标签组组合 #

实现思路 #

通过分组计数匹配的标签数量，对比标签组的总标签数，判断人员是否拥有标签组的全部标签。预计算标签组的标签总数可以避免重复统计，提升查询效率。

优化SQL #

-- 预计算每个标签组的总标签数
WITH tag_group_tag_counts AS (
    SELECT tag_group_id, COUNT(*) AS total_tags
    FROM tag_group_tags
    GROUP BY tag_group_id
)
SELECT 
    p.id AS person_id, 
    tg.id AS tag_group_id
FROM people p
CROSS JOIN tag_groups tg
JOIN tag_group_tag_counts tgtc ON tgtc.tag_group_id = tg.id
-- 匹配人员与标签组的共同标签
LEFT JOIN people_tags pt ON pt.people_id = p.id
LEFT JOIN tag_group_tags tgt ON tgt.tag_group_id = tg.id AND tgt.tag_id = pt.tag_id
GROUP BY p.id, tg.id, tgtc.total_tags
-- 匹配标签数等于标签组总标签数则符合条件
HAVING COUNT(tgt.tag_id) = tgtc.total_tags

2. 按tag_groups.sort升序取每个人员的首个匹配标签组 #

实现思路 #

先筛选出所有有效的人员-标签组组合（复用第一个查询的逻辑），再通过窗口函数按人员分组、按sort升序排名，取排名第一的记录。

优化SQL #

WITH tag_group_tag_counts AS (
    SELECT tag_group_id, COUNT(*) AS total_tags
    FROM tag_group_tags
    GROUP BY tag_group_id
),
valid_person_tag_groups AS (
    SELECT 
        p.id AS person_id, 
        tg.id AS tag_group_id,
        tg.sort
    FROM people p
    CROSS JOIN tag_groups tg
    JOIN tag_group_tag_counts tgtc ON tgtc.tag_group_id = tg.id
    LEFT JOIN people_tags pt ON pt.people_id = p.id
    LEFT JOIN tag_group_tags tgt ON tgt.tag_group_id = tg.id AND tgt.tag_id = pt.tag_id
    GROUP BY p.id, tg.id, tg.sort, tgtc.total_tags
    HAVING COUNT(tgt.tag_id) = tgtc.total_tags
),
ranked_groups AS (
    SELECT 
        person_id, 
        tag_group_id,
        -- 按人员分组，sort升序排名
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY sort ASC) AS rn
    FROM valid_person_tag_groups
)
-- 取每个人员的首个匹配标签组
SELECT person_id, tag_group_id
FROM ranked_groups
WHERE rn = 1

3. 左联标签组，包含所有人员，无匹配则tag_groups.id为null #

实现思路 #

基于第二个查询的结果，将人员表左联到排名后的首个匹配标签组记录；如果需要显示所有匹配组（无匹配则补充一条null记录），则用UNION ALL拼接无匹配的人员记录。

场景A：每个人员对应首个匹配标签组（无则null）

WITH tag_group_tag_counts AS (
    SELECT tag_group_id, COUNT(*) AS total_tags
    FROM tag_group_tags
    GROUP BY tag_group_id
),
valid_person_tag_groups AS (
    SELECT 
        p.id AS person_id, 
        tg.id AS tag_group_id,
        tg.sort
    FROM people p
    CROSS JOIN tag_groups tg
    JOIN tag_group_tag_counts tgtc ON tgtc.tag_group_id = tg.id
    LEFT JOIN people_tags pt ON pt.people_id = p.id
    LEFT JOIN tag_group_tags tgt ON tgt.tag_group_id = tg.id AND tgt.tag_id = pt.tag_id
    GROUP BY p.id, tg.id, tg.sort, tgtc.total_tags
    HAVING COUNT(tgt.tag_id) = tgtc.total_tags
),
ranked_groups AS (
    SELECT 
        person_id, 
        tag_group_id,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY sort ASC) AS rn
    FROM valid_person_tag_groups
)
-- 左联确保所有人员都被包含
SELECT 
    p.id AS person_id, 
    rg.tag_group_id
FROM people p
LEFT JOIN ranked_groups rg ON p.id = rg.person_id AND rg.rn = 1

场景B：每个人员显示所有匹配标签组，无匹配则显示一条null记录

WITH tag_group_tag_counts AS (
    SELECT tag_group_id, COUNT(*) AS total_tags
    FROM tag_group_tags
    GROUP BY tag_group_id
),
valid_person_tag_groups AS (
    SELECT 
        p.id AS person_id, 
        tg.id AS tag_group_id
    FROM people p
    CROSS JOIN tag_groups tg
    JOIN tag_group_tag_counts tgtc ON tgtc.tag_group_id = tg.id
    LEFT JOIN people_tags pt ON pt.people_id = p.id
    LEFT JOIN tag_group_tags tgt ON tgt.tag_group_id = tg.id AND tgt.tag_id = pt.tag_id
    GROUP BY p.id, tg.id, tgtc.total_tags
    HAVING COUNT(tgt.tag_id) = tgtc.total_tags
)
-- 拼接有效组合和无匹配的人员记录
SELECT person_id, tag_group_id FROM valid_person_tag_groups
UNION ALL
SELECT id AS person_id, NULL AS tag_group_id
FROM people p
WHERE NOT EXISTS (
    SELECT 1 FROM valid_person_tag_groups vptg WHERE vptg.person_id = p.id
)

性能优化关键点 #

• 给多对多中间表添加联合索引：people_tags(people_id, tag_id)、tag_group_tags(tag_group_id, tag_id)，加速连接查询。
• 预计算标签组的标签总数（CTE），避免重复执行子查询统计标签数量。
• 使用窗口函数ROW_NUMBER()替代子查询取最小排序值，大数据量下性能更优。

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