MySQL实现分组：同ID聚合且将不同ID分散至5个类别

Got it, let's break down how to solve this. The core goals here are twofold: first, keep all records with the same id in the same category, and second, spread distinct ids evenly across 5 categories. Then we need to format the result into the 5-column layout you showed. Here's how to do it in MySQL:

Step-by-Step Explanation & Query #

First, we'll assign a category to each unique id (to ensure same IDs stay together). Then we'll label each record's position within its category, and finally pivot the data into columns.

Here's the full query (replace your_table_name with your actual table name):

WITH id_category_mapping AS (
    -- Assign a unique category (1-5) to each distinct ID, spread evenly
    SELECT DISTINCT id,
           -- Use MOD + ROW_NUMBER to distribute IDs across 5 categories
           MOD(ROW_NUMBER() OVER (ORDER BY id), 5) + 1 AS category
    FROM your_table_name
),
record_with_row_num AS (
    -- Combine name + id, add category, and row number within each category
    SELECT 
        CONCAT(name, ' ', id) AS record,
        icm.category,
        ROW_NUMBER() OVER (PARTITION BY icm.category ORDER BY name) AS row_position
    FROM your_table_name t
    JOIN id_category_mapping icm ON t.id = icm.id
)
-- Pivot the data into 5 category columns
SELECT
    MAX(CASE WHEN category = 1 THEN record END) AS `cat 1`,
    MAX(CASE WHEN category = 2 THEN record END) AS `cat 2`,
    MAX(CASE WHEN category = 3 THEN record END) AS `cat 3`,
    MAX(CASE WHEN category = 4 THEN record END) AS `cat 4`,
    MAX(CASE WHEN category = 5 THEN record END) AS `cat 5`
FROM record_with_row_num
GROUP BY row_position
ORDER BY row_position;

How This Works: #

• id_category_mapping CTE: We take all unique ids, sort them, and use ROW_NUMBER() combined with MOD 5 to assign each a category from 1 to 5. This ensures distinct IDs are spread evenly across the 5 categories.
• record_with_row_num CTE: We join the original table with our category mapping to attach the category to each record. We also add a row number for each record within its category—this lets us align records vertically in the final pivot.
• Final Pivot: Using conditional aggregation (MAX(CASE ...)), we turn each category into a column. Grouping by row_position ensures records from the same "row" in each category line up, with empty cells showing as NULL (which MySQL displays as blank in the result, matching your example).

Quick Adjustments: #

• If you want random distribution of IDs instead of sorted, swap ORDER BY id in the first CTE with ORDER BY RAND().
• If you don't want the name and ID combined into one string, remove the CONCAT and adjust the pivot columns to display name and ID separately.

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