如何进行多表查询？基于指定表结构的技术咨询

嘿，针对你需要统计各国家用户多维度数据的需求，我结合你给出的表结构，整理了一套可以直接复用的SQL查询方案，涵盖你提到的所有统计指标：

WITH ranked_disciplines AS (
    SELECT
        c.country_id,
        ad.name AS discipline_name,
        COUNT(usd.id) AS subscribe_count,
        RANK() OVER (PARTITION BY c.country_id ORDER BY COUNT(usd.id) DESC) AS discipline_rank
    FROM countries c
    LEFT JOIN "user" u ON c.country_id = u.country_id
    LEFT JOIN user_subscribed_disciplines usd ON u.id = usd.user_id
    LEFT JOIN announcement_disciplin ad ON usd.discipline_id = ad.id
    GROUP BY c.country_id, ad.name
),
ranked_study_levels AS (
    SELECT
        c.country_id,
        csl.name AS study_level_name,
        COUNT(usl.id) AS subscribe_count,
        RANK() OVER (PARTITION BY c.country_id ORDER BY COUNT(usl.id) DESC) AS level_rank
    FROM countries c
    LEFT JOIN "user" u ON c.country_id = u.country_id
    LEFT JOIN user_subscribed_study_levels usl ON u.id = usl.user_id
    LEFT JOIN cat_study_levels csl ON usl.study_level_id = csl.id
    GROUP BY c.country_id, csl.name
)
SELECT
    c.short_name AS country_name,
    COUNT(u.id) AS total_users,
    SUM(CASE WHEN u.status = 2 THEN 1 ELSE 0 END) AS active_users,
    SUM(CASE WHEN u.status = 1 THEN 1 ELSE 0 END) AS inactive_users,
    COUNT(DISTINCT usd.user_id) AS discipline_subscribed_users,
    STRING_AGG(DISTINCT rd.discipline_name, ', ') AS top_disciplines,
    COUNT(DISTINCT usl.user_id) AS study_level_subscribed_users,
    STRING_AGG(DISTINCT rsl.study_level_name, ', ') AS top_study_levels
FROM countries c
LEFT JOIN "user" u ON c.country_id = u.country_id
LEFT JOIN user_subscribed_disciplines usd ON u.id = usd.user_id
LEFT JOIN ranked_disciplines rd ON c.country_id = rd.country_id AND rd.discipline_rank = 1
LEFT JOIN user_subscribed_study_levels usl ON u.id = usl.user_id
LEFT JOIN ranked_study_levels rsl ON c.country_id = rsl.country_id AND rsl.level_rank = 1
GROUP BY c.country_id, c.short_name
ORDER BY total_users DESC;

各统计项的实现说明 #

• 总用户/活跃/非活跃用户数：通过COUNT(u.id)统计总用户，用CASE WHEN配合SUM分别统计活跃（status=2）和非活跃（status=1）的用户数量，确保每个用户只被计数一次。
• 学科订阅用户数：用COUNT(DISTINCT usd.user_id)统计至少订阅过一个学科的用户（去重避免同一用户多次订阅被重复计数）。
• 热门学科：通过CTE ranked_disciplines 按国家分组，对每个学科的订阅量排序，取排名第一的学科；如果有多个学科订阅量并列第一，用STRING_AGG将它们拼接成逗号分隔的字符串。
• 学习阶段订阅用户数：逻辑和学科订阅用户数一致，通过COUNT(DISTINCT usl.user_id)统计去重后的订阅用户。
• 热门学习阶段：同样用CTE ranked_study_levels 按国家排序取订阅量最高的阶段，并列情况用STRING_AGG处理。

注意事项 #

1. 如果你的数据库不支持STRING_AGG（比如MySQL），可以替换成GROUP_CONCAT；
2. 若只需要返回单个热门学科/阶段（即使有并列），可以把RANK()替换成ROW_NUMBER()；
3. 为了提升查询效率，建议给以下字段创建索引：
   • user(country_id, status)
   • user_subscribed_disciplines(user_id, discipline_id)
   • user_subscribed_study_levels(user_id, study_level_id)

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