按顶级分类查看

社区 数据与报告
1

按顶级分类统计的浏览量

包括您的论坛自创立以来所有主题浏览量的总和。

/* 外层 SELECT 用于隐藏人工排序列，并据此进行排序。 */
SELECT "Category", "Views"
FROM (

    /* 各顶级分类的浏览量。 */
    SELECT 
        topcat AS "Category", 
        SUM(views) AS "Views",
        0 AS sortorder /* 人工排序列，用于排在最前面。 */
    
    FROM (
        /* 子级分类中的主题浏览量。 */
        SELECT
            topcat.name AS topcat, SUM(topics.views) AS views
        FROM topics
            INNER JOIN categories subcat ON topics.category_id = subcat.id
            INNER JOIN categories topcat ON subcat.parent_category_id = topcat.id
        GROUP BY topcat.name
        
        UNION
    
        /* 顶级分类中的主题浏览量（不包括子级分类）。 */
        SELECT
            topcat.name AS topcat, SUM(topics.views) AS views
        FROM topics 
            INNER JOIN categories topcat ON topics.category_id = topcat.id
        WHERE topcat.parent_category_id IS NULL
        GROUP BY topcat.name
    ) AS views_by_cat
    GROUP BY topcat
    
    UNION
    
    /* 在末尾添加总计行。 */
    SELECT 
        'GRAND TOTAL' AS "Category",
        SUM(topics.views) AS "Views",
        1 AS sortorder /* 人工排序列，用于排在最后面。 */
    FROM
        topics
    GROUP BY "Category"
    
    /* 按分类或浏览量对输出进行排序。启用以下其中一项： */
    /* ORDER BY topcat, subcat */
    ORDER BY "Views" DESC

) AS views_by_cat_with_total

ORDER BY sortorder, "Category"