MySQL 5.7多对多结构下按日期分组聚合查询需求

First, let's break down what we need to achieve:

• Link each entry to its Group A (main) category and Group B (sub) categories
• Aggregate counts per date, main category, and sub category
• Calculate both the sub-category count (subcat_count) and the total count of all sub-categories for that main category on the same date (total_count)

Since MySQL 5.7 doesn't support window functions (those arrived in 8.0), we'll use nested aggregate queries to get the exact format you need. Here's the working SQL:

SELECT 
    subcat_stats.entry_date,
    subcat_stats.cat_name,
    subcat_stats.subcat_name,
    subcat_stats.subcat_count,
    total_stats.total_count
FROM (
    -- Step 1: Calculate count per sub-category for each date + main category
    SELECT 
        DATE(FROM_UNIXTIME(ct.entry_date)) AS entry_date,
        main_cat.cat_name AS cat_name,
        sub_cat.cat_name AS subcat_name,
        COUNT(*) AS subcat_count
    FROM channel_titles ct
    -- Join to get the main category (Group A) for each entry
    JOIN category_posts cp_main 
        ON ct.entry_id = cp_main.entry_id
    JOIN categories main_cat 
        ON cp_main.cat_id = main_cat.cat_id
    JOIN category_groups cg_main 
        ON main_cat.group_id = cg_main.group_id 
        AND cg_main.group_name = 'Group A'
    -- Join to get all sub-categories (Group B) linked to the same entry
    JOIN category_posts cp_sub 
        ON ct.entry_id = cp_sub.entry_id
    JOIN categories sub_cat 
        ON cp_sub.cat_id = sub_cat.cat_id
    JOIN category_groups cg_sub 
        ON sub_cat.group_id = cg_sub.group_id 
        AND cg_sub.group_name = 'Group B'
    GROUP BY entry_date, cat_name, subcat_name
) subcat_stats
-- Join with total counts per date + main category
JOIN (
    -- Step 2: Calculate total sub-category count per date + main category
    SELECT 
        DATE(FROM_UNIXTIME(ct.entry_date)) AS entry_date,
        main_cat.cat_name AS cat_name,
        COUNT(*) AS total_count
    FROM channel_titles ct
    JOIN category_posts cp_main 
        ON ct.entry_id = cp_main.entry_id
    JOIN categories main_cat 
        ON cp_main.cat_id = main_cat.cat_id
    JOIN category_groups cg_main 
        ON main_cat.group_id = cg_main.group_id 
        AND cg_main.group_name = 'Group A'
    JOIN category_posts cp_sub 
        ON ct.entry_id = cp_sub.entry_id
    JOIN categories sub_cat 
        ON cp_sub.cat_id = sub_cat.cat_id
    JOIN category_groups cg_sub 
        ON sub_cat.group_id = cg_sub.group_id 
        AND cg_sub.group_name = 'Group B'
    GROUP BY entry_date, cat_name
) total_stats
    ON subcat_stats.entry_date = total_stats.entry_date
    AND subcat_stats.cat_name = total_stats.cat_name
-- Sort results to match your example
ORDER BY subcat_stats.entry_date DESC, subcat_stats.cat_name, subcat_stats.subcat_name;

How This Works: #

1. Subquery subcat_stats: This links each entry to its main Group A category and all associated Group B sub-categories, then groups by date, main category, and sub-category to get subcat_count (the number of entries for that specific sub-category on the date).
2. Subquery total_stats: This calculates the total number of sub-category entries per date and main category, which becomes total_count.
3. Final Join: We combine the two subqueries to attach the total count to each sub-category row, giving you the exact format you requested.

Testing with Your Sample Data: #

Running this query against your provided tables will return exactly the output you showed:

Notes: #

• We use JOIN instead of LEFT JOIN here because your sample data shows every entry has both a Group A and Group B category link. If you have entries missing either, switch to LEFT JOIN and add COALESCE() to handle NULL counts.
• The ORDER BY clause ensures results are sorted by date (newest first), then main category, then sub-category to match your example.

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