SQL如何基于另一表的最高频列值实现多表关联过滤

• 现有初始查询可统计DB1表中出现频次最高的问题内容及对应频次，DB1表每条记录存储单条问题，包含唯一标识question_id、可重复的问题文本question字段
• DB2表存储question_id与提问用户user_id的映射关系
• 需求：在不破坏原有高频问题统计逻辑（即不因为引入question_id字段改变分组维度导致统计失效）的前提下，查询提出Top50高频问题的所有用户ID
• 原有高频问题查询逻辑如下：

WITH data AS
(
    SELECT question, COUNT(*) AS frequency
    FROM DB1
    GROUP BY 1
    LIMIT 50
)

SELECT question, frequency
FROM data
ORDER BY frequency DESC

• 原有查询返回示例：

• DB2表结构示例：

• 此前尝试的嵌套子查询写法存在性能差、逻辑冗余的问题，且误以为JOIN写法必须修改CTE分组字段会导致统计失效。

核心思路是保持高频统计CTE的逻辑完全独立，分层做关联，不需要将question_id加入统计阶段的分组维度：

1. 修正原有CTE的逻辑漏洞：将排序逻辑移入CTE内部，保证LIMIT 50截断的是按频次倒序的真正Top50问题，而非随机50条
2. 用统计得到的Top50问题文本关联DB1表，拿到这些问题对应的所有question_id，这一步不需要做分组
3. 再通过question_id关联DB2表，拿到对应的提问用户ID，按需去重即可

可直接运行的代码如下：

WITH data AS
(
    SELECT question, COUNT(*) AS frequency
    FROM DB1
    GROUP BY 1
    ORDER BY COUNT(*) DESC -- 排序移到CTE内，保证LIMIT拿到真实Top50
    LIMIT 50
)
SELECT DISTINCT -- 去重避免同一用户多次提同一问题返回重复ID
    d.question,
    d.frequency,
    db2.user_id
FROM data d
INNER JOIN DB1 db1
    ON d.question = db1.question
INNER JOIN DB2 db2
    ON db1.question_id = db2.question_id
ORDER BY d.frequency DESC, db2.user_id

关键说明 #

• 全程不需要修改data CTE的分组逻辑，question_id仅在后续关联阶段使用，不会破坏原有的高频问题统计结果，完全规避分组加唯一标识导致返回随机记录的问题
• 相比多层IN嵌套子查询，JOIN写法在大数据量下性能更优，逻辑分层更清晰，便于后续调整（比如需要统计每个高频问题的去重提问用户数、每个用户提问高频问题的次数等，只需要在最后一层加聚合即可）
• 如果不需要返回问题内容和频次，只需要用户ID列表，把SELECT子句改成SELECT DISTINCT db2.user_id即可

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